- The Range Object
- Spe的语法cifying a Range
- Named Ranges
- Shortcut for Referencing Ranges
- 引用其他表的范围
- 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 Select a Data Range
- Using the Areas Collection to Return a Noncontiguous Range
- Referencing Tables
- Next Steps
引用其他表的范围
Switching between sheets by activating the needed sheet slows down your code. To avoid this, refer to a sheet that is not active by first referencing theWorksheetobject:
Worksheets("Sheet1").Range("A1")
This line of code references Sheet1 of the active workbook even if Sheet2 is the active sheet.
To reference a range in another workbook, include theWorkbookobject, theWorksheetobject, and then theRangeobject:
Workbooks("InvoiceData.xlsx").Worksheets("Sheet1").Range("A1")
To use theRangeproperty as an argument within anotherRangeproperty, 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? AlthoughRange("A1"), Range("A7")is meant to refer to the sheet at the beginning of the code line (Sheet2), Excel does not assume that you want to carry theWorksheet对象引用在to these otherRangeobjects and assumes that they refer to the active sheet, 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 difficult to read! Thankfully, there is a simpler way, usingWith...End With:
With Worksheets("Sheet2") WorksheetFunction.Sum(.Range(.Range("A1"), .Range("A7"))) End With
Notice now that there is a.Rangein your code, but without the preceding object reference. That’s becauseWith 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 closestWithstatement and uses that as the object reference.