Excel – VBA – Referring To Ranges In Your Code

Send Us a Sign! (Contact Us!)

--> (Word)	--> (PDF)	--> (Epub)	--> (Text)
--> (XML)	--> (OpenOffice)		--> (XPS)

Show More Info

This article has been read [post_view] times. This article has been read [post_view time="day"] times today. This article has been read [post_view time="month"] times this month. This article has been read [post_view time="year"] times this year. This article has 634 total words. (Including header) [tweet]

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 [gs worksheet] is to specify the range directly. Here's an example that assigns the value 1 to range A1:C12 on Sheet1 in a [gs 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

[tab:Cells Method]

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

[tab:Offset Method]

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 [gs variable]s, rather than numbers. The [gs 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 

[tab:END]

SOURCE	LINK (Spreadsheetpage.com)	LANGUAGE	ENGLISH