Excel 2013 VBA and Macros: Referring to Ranges
- The Range Object
- Syntax to Specify a Range
- Named Ranges
- Shortcut for Referencing Ranges
- Referencing Ranges in Other Sheets
- Referencing a Range Relative to Another Range
- Use the Cells Property to Select a Range
- Use the Offset Property to Refer to a Range
- Use the Resize Property to Change the Size of a Range
- Use the Columns and Rows Properties to Specify a Range
- Use the Union Method to Join Multiple Ranges
- Use the Intersect Method to Create a New Range from Overlapping Ranges
- Use the ISEMPTY Function to Check Whether a Cell Is Empty
- Use the CurrentRegion Property to Select a Data Range
- Use the Areas Collection to Return a Noncontiguous Range
- Referencing Tables
- Next Steps
From the author of
From the author of
From the author of
A range can be a cell, a row, a column, or a grouping of any of these. The RANGE object is probably the most frequently used object in Excel VBA—after all, you are manipulating data on a sheet. Although a range can refer to any grouping of cells on a sheet, it can refer to only one sheet at a time. If you want to refer to ranges on multiple sheets, you must refer to each sheet separately.
The Range Object
The following is the Excel object hierarchy:
Application > Workbook > Worksheet > Range
The Range object is a property of the Worksheet object. This means it requires that a sheet be active or it must reference a worksheet. Both of the following lines mean the same thing if Worksheets(1) is the active sheet:
Range("A1")
Worksheets(1).Range("A1")
There are several ways to refer to a Range object. Range("A1") is the most identifiable because that is how the macro recorder refers to it. However, each of the following is equivalent when referring to a range:
Range("D5")
[D5]
Range("B3").Range("C3")
Cells(5,4)
Range("A1").Offset(4,3)
Range("MyRange") 'assuming that D5 has a Name 'of MyRange
Which format you use depends on your needs. Keep reading—it will all make sense soon!