Specifying Cell Coordinates

Cell coordinates can be specified in different ways depending on what you are selecting and which object you are using.

WORKSHEET OBJECT

Cell ranges can be specified in various ways for the 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 Columns method:

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

  2. Columns("A:E") is a one argument range of columns

  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 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

WKSRANGE OBJECT

Also, the Cells, Columns, and Rows methods work slightly differently when invoked on a WksRange object than when invoked on a Worksheet 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 = Grapher.Documents.Add(grfWksDoc)

  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:

  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.

 

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"

 

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"

See Also

Introducing Scripter