- 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
Referencing Ranges in Other Sheets
Switching between sheets by activating the needed sheet can dramatically slow down your code. To avoid this slowdown, you can refer to a sheet that is not active by first referencing the Worksheet object:
Worksheets("Sheet1").Range("A1")
This line of code references Sheet1 of the active workbook even if Sheet2 is the active sheet.
If you need to reference a range in another workbook, include the Workbook object, the Worksheet object, and then the Range object:
Workbooks("InvoiceData.xlsx").Worksheets("Sheet1").Range("A1")
Be careful if you use the Range property as an argument within another Range property. You must identify the range fully each time. For example, suppose that Sheet1 is your active sheet and you need to total data from Sheet2:
WorksheetFunction.Sum(Worksheets("Sheet2").Range(Range("A1"), Range("A7")))
This line does not work. Why not? Because Range("A1"), Range("A7") is meant to refer to the sheet at the beginning of the code line. However, Excel does not assume that you want to carry the Worksheet object reference over to these other Range objects and assumes they refer to Sheet1. So what do you do? Well, you could write this:
WorksheetFunction.Sum(Worksheets("Sheet2").Range(Worksheets("Sheet2"). _
Range("A1"), Worksheets("Sheet2").Range("A7")))
But this not only is a long line of code but also is difficult to read! Thankfully, there is a simpler way, using With...End With:
With Worksheets("Sheet2")
WorksheetFunction.Sum(.Range(.Range("A1"), .Range("A7")))End With
Notice now that there is a .Range in your code, but without the preceding object reference. That’s because With Worksheets("Sheet2") implies that the object of the range is the worksheet. Whenever Excel sees a period without an object reference directly to the left of it, it looks up the code for the closest With statement and uses that as the object reference.