- The Range Object
- Using the Top-Left and Bottom-Right Corners of a Selection to Specify a Range
- Referencing Ranges in Other Sheets
- Referencing a Range Relative to Another Range
- Using the Cells Property to Select a Range
- Using the Offset Property to Refer to a Range
- Using the Resize Property to Change the Size of a Range
- Using the Columns and Rows Properties to Specify a Range
- Using the Union Method to Join Multiple Ranges
- Using the Intersect Method to Create a New Range from Overlapping Ranges
- Using the IsEmpty Function to Check Whether a Cell Is Empty
- Using the CurrentRegion Property to Quickly Select a Data Range
- Using the Areas Collection to Return a Non-contiguous Range
- Next Steps
This chapter is from the book
This chapter is from the book
This chapter is from the book
Using the Top-Left and Bottom-Right Corners of a Selection to Specify a Range
There are two acceptable syntaxes for the Range command. To specify a rectangular range in the first syntax, you specify the complete range reference just as you would in a formula in Excel:
Range("A1:B5").Select
In the alternative syntax, you specify the top-left corner and bottom-right corner of the desired rectangular range. In this syntax, the equivalent statement might be
Range("A1", "B5").Select
For either corner, you can substitute a named range, the Cells function, or the ActiveCell property. This line of code selects the rectangular range from A1 to the active cell:
Range("A1", ActiveCell).Select
The following statement would select from the active cell to five rows below the active cell and two columns to the right:
Range(ActiveCell, ActiveCell.Offset(5, 2)).Select
Shortcut for Referencing Ranges
A shortcut is available when referencing ranges. It uses [square brackets], as shown in Table 3.1:
Table 3.1 Shortcuts for Referring to Ranges
|
Standard Method |
Shortcut |
|
Range("D5") |
[D5] |
|
Range("A1:D5") |
[A1:D5] |
|
Range ("A1:D5," "G6:I17") |
[A1:D5, G6:I17] |
|
Range("MyRange") |
[MyRange] |
Named Ranges
You've probably already used Named ranges on your sheets and in formulas. You can also use them in VBA.
To refer to the range "MyRange" in Sheet1, do this:
Worksheets(1).Range("MyRange")
Notice that the Name of the range is in quotes—unlike the use of Named ranges in formulas on the sheet itself. If you forget to put the Name in quotes, Excel thinks you are referring to a variable in the program, unless you are using the shortcut syntax discussed in the previous section, in which case, quotes are not used.