- 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
Referencing Ranges in Other Sheets
Switching between sheets by activating the needed sheet can drastically slow down your code. Instead, you can refer to a sheet that is not active by referencing the Worksheet object first:
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, then include the Workbook object, the Worksheet object, and then the Range object:
Workbooks("InvoiceData.xls").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. Let's say that Sheet1 is your active sheet and you need to total data on Sheet2:
WorksheetFunction.Sum(Worksheets("Sheet2").Range(Range("A1"), Range("A7")))
This line does not work. Why? Because Range(Range("A1"), Range("A7")) refers to an extra range at the beginning of the code line. Excel does not assume that you want to carry the Worksheet object reference over to the other Range objects. 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 is not only a long line of code, it is difficult to read! Thankfully, there is a simpler way, 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.