Referring To Ranges In Your VBA Code
Newcomers to VBA are often confused about how to refer to ranges on a worksheet. This confusion is somewhat justified, because VBA offers several different ways to refer to ranges. In this document I provide an overview of these techniques:
- Referring to ranges directly
- Referring to ranges by using the Cells method
- Referring to ranges by using the Offset method
Referring to ranges directly
Perhaps the most common way to refer to a range on a worksheet is to specify the range directly. Here's an example that assigns the value 1 to range A1:C12 on Sheet1 in a workbook named MyBook:
Workbooks("MyBook").Sheets("Sheet1").Range("A1:C12").Value = 1
Notice that this is a fully qualified reference. This statement will work regardless of which sheet is active. If Sheet1 is the active sheet, the statement can be simplified as:
Range("A1:C12").Value = 1
If the range has a name, you can use the name in your statement:
Range("myrange").Value = 1
You can also refer to a range by specifying the upper left and the lower right cell. Here's an example that assigns a value to all cells in the range A1:D12 on the active worksheet.
Range(Range("A1"), Range("D12")).Value = 99
Referring to ranges by using the Cells method
In Excel, the Range object has a method called Cells. Note that Cells is a method - not an object. When the Cells method is evaluated, it returns an object (specifically, a Range object).
The Cells method takes two arguments: the row and the column. The following statement assigns the value 1 to cell C2 on Sheet1:
Worksheets("Sheet1").Cells(2,3).Value = 1
You can also use the Cells method to refer to a larger range. The following statement assigns the value 1 to A1:J12 on the active worksheet:
Range(Cells(1,1), Cells(12,10)).Value = 1
In the preceding examples, the arguments for Cells were actual numbers. The advantage of using the Cells method becomes apparent when you use variables as the arguments. The subroutine below fills a 10X10 range (rowwise) with consecutive numbers from 1 to 100.
Sub FillRange() Num = 1 For Row = 1 To 10 For Col = 1 To 10 Sheets("Sheet1").Cells(Row, Col).Value = Num Num = Num + 1 Next Col Next Row End Sub
Referring to ranges by using the Offset method
The Offset method is another useful way to refer to ranges. The Offset method returns a Range object, and takes two arguments. The first argument represents the number of rows to offset; the second represents the number of columns to offset.
The following statement assigns the value 1 to the cell that is one row below cell C2 and two cells to the right of C2 (i.e., cell E3):
Range("C2").Offset(1,2).Value = 1
The Offset method is most useful when the arguments are variables, rather than numbers. The subroutine below fills a 10X10 range (rowwise) with consecutive numbers from 1 to 100.
Sub FillRange2() Num = 1 For Row = 0 To 9 For Col = 0 To 9 Sheets("Sheet1").Range("A1").Offset(Row,Col).Value = Num Num = Num + 1 Next Col Next Row End Sub
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
Browse Tips by Category
Search for Tips
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks