- The Range Object
- 语法指定一个范围
- Named Ranges
- Shortcut for 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 Select a Data Range
- Using the Areas Collection to Return a Noncontiguous Range
- Referencing Tables
- Next Steps
Using theCellsProperty to Select a Range
TheCellsproperty refers to all the cells of the specifiedRangeobject, which can be a worksheet or a range of cells. For example, this line selects all the cells of the active sheet:
Cells.Select
Using theCells财产的Rangeobject might seem redundant:
Range("A1:D5").Cells
This line refers to the originalRangeobject. However, theCellsproperty has anItemproperty that makes theCellsproperty very useful. TheItemproperty enables you to refer to a specific cell relative to theRangeobject.
The syntax for using theItem财产的Cellsproperty is as follows:
Cells.Item(Row,Column)
You must use a numeric value forRow, but you may use the numeric value or string value forColumn. Both of the following lines refer to cell C5:
Cells.Item(5,"C") Cells.Item(5,3)
Because theItemproperty is the default property of theRangeobject, you can shorten these lines as follows:
Cells(5,"C") Cells(5,3)
t的能力o use numeric values for parameters is particularly useful if you need to loop through rows or columns. The macro recorder usually uses something likeRange("A1").Selectfor a single cell andRange("A1:C5").Select一系列的细胞。如果你正在学习代码only from the recorder, you might be tempted to write code like this:
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row For i = 1 to FinalRow Range("A" & i & ":E" & i).Font.Bold = True Next i
This little piece of code, which loops through rows and bolds the cells in columns A through E, is awkward to read and write. But how else can you do it? Like this:
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row For i = 1 to FinalRow Cells(i,"A").Resize(,5).Font.Bold = True Next i
Instead of trying to type the range address, the new code uses theCellsandResizeproperties to find the required cell, based on the active cell. See the “Using theResizeProperty to Change the Size of a Range” section later in this chapter, for more information on theResizeproperty.
You can use theCellsproperties for parameters in theRangeproperty. The following refers to the range A1:E5:
Range(Cells(1,1),Cells(5,5))
This is particularly useful when you need to specify variables with a parameter, as in the previous looping example.