Specifying Ranges
There are many ways to specify a range in VBA. Below are some examples.
[A1]
[A1:B2]
[A:A]
[1:1]
[RangeName]
Range("A1")
Range("A1:B2")
Range("A:B")
Range("1:2")
Range(Cells(Row1, Column1), Cells(Row2, Column2))
Range(RangeVariable, RangeVariable)
Range("RangeName")
Cells(Row, Column)
Cells(Row, "ColumnLetter") (Important: see note at the end of this article about a bug in Excel 2003 and earlier)
RangeVariable.Range("C1:E1")
RangeVariable.EntireRow.Cells(1, "Z") (Important: see note at the end of this article about a bug in Excel 2003 and earlier)
RangeVariable.EntireColumn.Cells(10, 1)
Columns(1)
Columns("A") (Important: see note at the end of this article about a bug in Excel 2003 and earlier)
Columns("A:B") (Important: see note at the end of this article about a bug in Excel 2003 and earlier)
Rows(1)
Rows("1:2")
When using a ColumnLetter parameter with the Cells or Columns property, the letter is translated into an absolute column number.
Performance Note
The bracketed method can take longer to process than the other methods because it uses the Evaluate function. On faster machines, there is very little difference, but on slower machines the difference can be as much as twice as long. The most efficient method is to use only integers (rows and columns) with properties such as Cells, Rows, and Columns.
All of the above referencing methods except for the bracketed method ([A1]) can be used on any previously defined range object. When used in this manner, the row and column are treated as offsets into the range. They are also not limited to the parent object’s range and thus can reference cells beyond the range’s boundary. Below are some examples.
[C1:E1].Cells(1, "D") equals [C1:E1].Cells(1, 4) or [F1]
[C5].Columns("B") equals [C5].Columns(2) or [D5]
[C5].Rows(4).Columns(2) equals [D8]
Note that the Cells method must be used when indexing into a range derived from the Rows or Columns collections or the EntireRow and EntireColumn objects because any single indexed reference without the Cells method offsets the range by the “index” number of columns or rows which may not be desired:
Columns("B").Resize(10)(2) will return [C1:C10] so use Columns("B").Resize(10).Cells(2) to return [B2]
Using a double index (row, column) into a range derived from the Rows or Columns collections or the EntireRow and EntireColumn objects will fail with a 1004 error:
Rows(2)(1, 3) will fail so use Rows(2).Cells(1, 3)
[B2].EntireRow(1, "C") will fail so use [B2].EntireRow.Cells(1, "C")
Also note that the cells property must be used when indexing into a range specified with square brackets:
[B10:B20](23) will fail so use [B10:B20].Cells(5)
EntireColumn or EntireRow Method
If starting with a range that does not start in row 1 or column A and an absolute row or column is desired, use the EntireColumn or EntireRow method:
[C5].EntireRow.Cells(1, "D") equals [D5]
[C5].EntireRow.Columns("D") equals [D5]
[C5].EntireColumn.Cells(10, 1) equals [C10]
[C5].EntireColumn.Rows(10) equals [C10]
Ranges
When passing range variables to the Range method the top left cell of the first parameter and the bottom right cell of the second parameter are used and the result is the smallest rectangular range encompasing both range parameters:
Range([B2:C3], [D4:E5)] equates to B2:E5
Use commas to create unions:
[A1,B1,C1:C5]
Range("A1,B1,C1:C5")
[RangeName1, RangeName2]
Note that the text passed to Range and in square brackets cannot be longer than 255 characters.
Intersections and Offsets
Use spaces to create intersections:
[1:2 A:B] equates to A1:B2
To offset from a particular cell:
[B2].Cells(2, 3) equates to [C4] and is the same as [B2].Offset(1, 2)
To double offset from a particular cell:
[B2].Cells(2, 3)(4, 1) translates to [D6] and is the same as [B2].Offset(1, 2).Offset(3, 0)
To index into a range of cells starting from the top left cell and moving across each row from left to right and then down each row from top to bottom:
[B2:D4].Cells(Index)
[B2:D4].Cells(1) equates to [B2]
[B2:D4].Cells(5) equates to [C3]
This technique works for any shape of range. Note that indexing continues down past the end of the specified range:
[B2:D4].Cells(10) equates to [B5]
Top left cell of a range:
Target(1, 1)
Bottom right cell of a range:
Target(Target.Rows.Count, Target.Columns.Count)
Nth row or column of a range:
Target.Rows(N)
Target.Columns(N)
Working with all rows or columns in a range:
For Each Row In Target.Rows
Row(1, 1)
Row(1, 2)
Next Row
For Each Column In Target.Columns
Column(1, 1)
Column(2, 1)
Next Column
Fail Fixes with Set MyRange
Note that the Columns and Cells collections fail when passed a column letter and the active sheet is a chart or there is no visible workbook. This bug was resolved in Excel 2007. The workaround is to use the Range method. The problem occurs even if a specific parent expression is used which fully qualifies a worksheet. The following examples will fail when a chart sheet is active or there is no visible workbook:
Set MyRange = ThisWorkbook.Worksheets(1).Columns("A")
Set MyRange = ThisWorkbook.Worksheets(1).Cells(1, "A")
Set MyRange = ThisWorkbook.Worksheets(1).Range("A1:C10").Columns("A")
Set MyRange = ThisWorkbook.Worksheets(1).Range("A1:C10").Cells(1, "A")
These, however, will work:
Set MyRange = ThisWorkbook.Worksheets(1).[A1:IV65536].Columns("A")
Set MyRange = ThisWorkbook.Worksheets(1).[A1:IV65536].Cells(1, "A")
Set MyRange = ThisWorkbook.Worksheets(1).[A1:IV65536].Range("A1:C10").Columns("A")
Set MyRange = ThisWorkbook.Worksheets(1).[A1:IV65536].Range("A1:C10").Cells(1, "A")
As will these:
Set MyRange = ThisWorkbook.Worksheets(1).Columns(1)
Set MyRange = ThisWorkbook.Worksheets(1).Cells(1, 1)
Set MyRange = ThisWorkbook.Worksheets(1).Range("A1:C10").Columns(1)
Set MyRange = ThisWorkbook.Worksheets(1).Range("A1:C10").Cells(1, 1)