Specifying Cell Coordinates

Cell ranges can be specified in various ways for the WksDocument Cells and Range methods:

[1] Cells("A1") is a one argument single cell

[2] Cells("A1:C5") is a one argument range of cells

[3] Cells("A:E) is a one argument range of whole-columns

[4] Cells("1:5") is a one argument range of whole-rows

[5] Cells(Range object) is a one argument range of cells

[6] Cells(1,"A") -or- Cells(1,1) is a two argument single cell

[7] Cells("A1","C5") is a two argument range of cells

[8] Cells(1,"A",5,"C") -or- Cells(1,1,5,3) is a four argument range of cells

Column ranges can be specified in various ways for the WksDocument Columns method:

[1] Columns(1,5) or ("A","E) is a two argument range of columns

[2] Columns(A:

[3] Columns("A1:E1") is a one argument range of columns [the row coordinates are ignored]

[4] Columns(Range object) is a one argument range of columns [the row coordinates are ignored]

[5] Columns("A5") is a one argument single column [the row coordinate is ignored]

[6] Columns(1) is a one argument single column

Row ranges can be specified in various ways for the WksDocument Rows method:

[1] Rows(1,5) is a two argument range of rows

[2] Rows("1:5") is a one argument range of rows

[3] Rows("A5:A10") is a one argument range of rows [the column coordinates are ignored]

[4] Rows(Range object) is a one argument range of rows [the column coordinates are ignored]

[5] Rows("A5") is a one argument single row [the column coordinate is ignored]

[6] Rows(1) is a one argument single row

Also, the Cells, Columns, and Rows methods work slightly differently when invoked on a WksRange object than when invoked on a WksDocument object. When invoked on a WksRange object, the coordinates are relative to the upper-left corner of the range. For example, Range.Cells(A1) refers to whatever the upper-left corner of the Range happens to be, like so:

Set Wks = Surfer.Documents.Add(srfDocWks)

Set RangeObject1 = Wks.Cells(C5:E10)

' RangeObject2 now contains the cell "C5"

Set RangeObject2 = RangeObject1.Cells("A1")

 

' RangeObject3 now contains the cell "C5"

Set RangeObject3 = RangeObject1.Cells(1,1)

 

' RangeObject4 now contains the cell "D6"

Set RangeObject4 = RangeObject1.Cells(2,2)

In addition, you can use a single numeric argument in the Range.Cells() method to sequentially access each cell in the range, like so:

' Note: RangeObject1 equals C5:E10

Set RangeObject5 = RangeObject1.Cells(1) ; cell "C5"

Set RangeObject6 = RangeObject1.Cells(2) ; cell "D5"

Set RangeObject7 = RangeObject1.Cells(3) ; cell "E5"

' There are three cells in the first row of RangeObject1.

' Cell #4 is in the second row…

Set RangeObject8 = RangeObject1.Cells(4) ; cell "C6"

Set RangeObject9 = RangeObject1.Cells(5) ; cell "D6"

Set RangeObject10 = RangeObject1.Cells(6) ; cell "E6"

' Cell #7 is in the third row…

Set RangeObject11 = RangeObject1.Cells(7) ; cell "C7"

There are some special cases when the WksRange objects’ Cells, Columns, and Rows methods are called. The behavior for these special cases is explained in these notes:

WksRange.Cells() Method:

[1] Coordinates are relative to the top, left of the current (base) range

[2] The returned range can extend beyond the original range

[3] Rows are limited to the original range if a whole-column sub-range is specified

[4] Columns are limited to the original range if a whole-row sub-range is specified

[5] Cells are indexed across and then down

Examples:

Item	Base Range	Specified Sub-Range	Range Returned
[1]	Wks.Range(B10:C20).	Cells("A1")	"B10"
[2]	Wks.Range(B10:C20).	Cells("A1:C30")	"B10:D39"
[3]	Wks.Range(B10:C20).	Cells("A:C")	"B10:D20"
[4]	Wks.Range("B10:C20").	Cells("1:5")	"B10:C14"
[5]	Wks.Range("B10:C20").	Cells(n)	n=1 "B10", n=2 "C10", n=3 "B11", etc.

WksRange.Rows Method

Columns are limited to the original range (the same as if a whole-row sub-range were supplied to the Range.Cells method).

Example:

Base Range	Specified Sub-Range	Range returned
Wks.Range("B10:C20").	Rows("1:5")	"B10:C14"

WksRange.Columns Method

Rows are limited to the original range (the same as if a whole-column sub-range were supplied to the Range.Cells method)

Example:

Base Range	Specified Sub-Range	Range returned
Wks.Range("B10:C20").	Columns("A:C")	"B10:D20"