Range 对象 (Excel)Range object (Excel)

表示一个单元格、一行、一列、一个包含单个或若干连续单元格区域的选定单元格范围,或者一个三维区域。Represents a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3D range.

备注

有兴趣开发跨多个平台扩展 Office 体验的解决方案吗?Interested in developing solutions that extend the Office experience across multiple platforms? 查看新的 Office 外接程序模型Check out the new Office Add-ins model. 与 VSTO 外接程序和解决方案相比, Office 外接程序的占地面积较小, 您可以使用几乎任何 web 编程技术 (如 HTML5、JavaScript、CSS3 和 XML) 构建它们。Office Add-ins have a small footprint compared to VSTO Add-ins and solutions, and you can build them by using almost any web programming technology, such as HTML5, JavaScript, CSS3, and XML.

说明Remarks

有关返回 Range 对象的下述属性和方法,可查看示例部分:The following properties and methods for returning a Range object are described in the Example section:

  • Worksheet 对象的 RangeCells 属性Range and Cells properties of the Worksheet object
  • Range 对象的 RangeCells 属性Range and Cells properties of the Range object
  • Range 对象的 Offset 属性Offset property of the Range object
  • Application 对象的 Union 方法Union method of the Application object

示例Example

使用 Range (arg) 可返回一个 Range 对象,它表示单个单元格或单元格区域;其中 arg 对范围进行命令。Use Range (arg), where arg names the range, to return a Range object that represents a single cell or a range of cells. 下例将单元格 A1 的值赋给单元格 A5。The following example places the value of cell A1 in cell A5.

Worksheets("Sheet1").Range("A5").Value = _ 
    Worksheets("Sheet1").Range("A1").Value

下例通过为区域中的每个单元格设置公式,在区域 A1:H8 中填充随机数。The following example fills the range A1:H8 with random numbers by setting the formula for each cell in the range. 在没有对象限定符的情况下使用它时(对象位于句点左侧),Range 属性将返回活动工作表上的一个区域。When it's used without an object qualifier (an object to the left of the period), the Range property returns a range on the active sheet. 如果活动工作表不是一个工作表,则该方法将失败。If the active sheet isn't a worksheet, the method fails.

使用 Worksheet 对象的 Activate 方法在你使用 Range 属性之前激活工作表,而不使用显式对象限定符。Use the Activate method of the Worksheet object to activate a worksheet before you use the Range property without an explicit object qualifier.

Worksheets("Sheet1").Activate 
Range("A1:H8").Formula = "=Rand()"    'Range is on the active sheet

下例清除区域名为“Criteria”的区域中的内容。The following example clears the contents of the range named Criteria.

备注

如果使用文本参数指定区域地址,则必须以 A1 样式记号指定该地址(不能用 R1C1 样式记号)。If you use a text argument for the range address, you must specify the address in A1-style notation (you cannot use R1C1-style notation).

Worksheets(1).Range("Criteria").ClearContents

使用 Cells (row, column) 可返回一个单元格,其中 row 是行的索引号,column 是列的索引号。Use Cells (row, column), where row is the row index and column is the column index, to return a single cell. 下例将单元格 A1 的值设置为 24。The following example sets the value of cell A1 to 24.

Worksheets(1).Cells(1, 1).Value = 24

下例设置单元格 A2 的公式。The following example sets the formula for cell A2.

ActiveSheet.Cells(2, 1).Formula = "=Sum(B1:B5)"

虽然也可使用 Range("A1") 返回单元格 A1,但 Cells 属性有时候更方便,因为可将变量用于行或列。Although you can also use Range("A1") to return cell A1, there may be times when the Cells property is more convenient because you can use a variable for the row or column. 下例在 Sheet1 上创建列和行标题。The following example creates column and row headings on Sheet1. 请注意,在激活工作表之后,可在没有显式工作表声明的情况下使用 Cells 属性(它返回活动工作表上的一个单元格)。Be aware that after the worksheet has been activated, the Cells property can be used without an explicit sheet declaration (it returns a cell on the active sheet).

备注

虽然可以使用 Visual Basic 字符串函数转换 A1 样式引用,但使用 Cells(1, 1) 表示法更为简便(而且也是更好的编程习惯)。Although you could use Visual Basic string functions to alter A1-style references, it is easier (and better programming practice) to use the Cells(1, 1) notation.

Sub SetUpTable() 
Worksheets("Sheet1").Activate 
For TheYear = 1 To 5 
    Cells(1, TheYear + 1).Value = 1990 + TheYear 
Next TheYear 
For TheQuarter = 1 To 4 
    Cells(TheQuarter + 1, 1).Value = "Q" & TheQuarter 
Next TheQuarter 
End Sub

使用 expression.Cells (row, column) 可返回区域的一部分,其中 expression 是返回 Range 对象的表达式, rowcolumn 相对于区域的左上角。Use_expression_.Cells (row, column), where expression is an expression that returns a Range object, and row and column are relative to the upper-left corner of the range, to return part of a range. 下例设置单元格 C5 的公式。The following example sets the formula for cell C5.

Worksheets(1).Range("C5:C10").Cells(1, 1).Formula = "=Rand()"

使用 Range(cell1, cell2) 可返回一个 Range 对象,其中 cell1cell2 是指定起始和终止单元格的 Range 对象。Use Range (cell1, cell2), where cell1 and cell2 are Range objects that specify the start and end cells, to return a Range object. 下例设置单元格 A1:J10 的边框线条样式。The following example sets the border line style for cells A1:J10.

备注

请注意,如果要对 Cells 属性应用前导 With 语句的结果,则每次出现 Cells 属性时其前面必须具有句点。Be aware that the period in front of each occurrence of the Cells property is required if the result of the preceding With statement is to be applied to the Cells property. 在本例中,它表示单元格位于工作表 1 上(不带句点,Cells 属性会返回活动工作表上的单元格)。In this case, it indicates that the cells are on worksheet one (without the period, the Cells property would return cells on the active sheet).

With Worksheets(1) 
    .Range(.Cells(1, 1), _ 
        .Cells(10, 10)).Borders.LineStyle = xlThick 
End With

使用 Offset(row, column) 可返回相对于另一区域在指定偏移量处的区域,其中 rowcolumn 分别是行偏移量和列偏移量。Use Offset (row, column), where row and column are the row and column offsets, to return a range at a specified offset to another range. 下例选择从当前选定区域左上角的单元格下移 3 行和右移 1 列所得的单元格。The following example selects the cell three rows down from and one column to the right of the cell in the upper-left corner of the current selection. 不能选择活动工作表上没有的单元格,因此必须先激活工作表。You cannot select a cell that is not on the active sheet, so you must first activate the worksheet.

Worksheets("Sheet1").Activate 
  'Can't select unless the sheet is active 
Selection.Offset(3, 1).Range("A1").Select

使用 Union (range1, range2, ...) 可返回多区域范围,即返回由两个或更多连续单元格区域构成的范围。Use Union (range1, range2, ...) to return multiple-area ranges—that is, ranges composed of two or more contiguous blocks of cells. 下例创建一个定义为区域 A1:B2 和 C3:D4 的合并区域的对象,然后选择所定义的区域。The following example creates an object defined as the union of ranges A1:B2 and C3:D4, and then selects the defined range.

Dim r1 As Range, r2 As Range, myMultiAreaRange As Range 
Worksheets("sheet1").Activate 
Set r1 = Range("A1:B2") 
Set r2 = Range("C3:D4") 
Set myMultiAreaRange = Union(r1, r2) 
myMultiAreaRange.Select

如果使用包含多个区域的选定范围,则 Areas 属性很有用。If you work with selections that contain more than one area, the Areas property is useful. 它会将所选定的多区域范围划分为单个 Range 对象,然后以集合的形式返回对象。It divides a multiple-area selection into individual Range objects and then returns the objects as a collection. 可对所返回的集合使用 Count 属性,以验证包含多个区域的选定范围(如下例所示)。You can use the Count property on the returned collection to verify a selection that contains more than one area, as shown in the following example.

Sub NoMultiAreaSelection() 
    NumberOfSelectedAreas = Selection.Areas.Count 
    If NumberOfSelectedAreas > 1 Then 
        MsgBox "You cannot carry out this command " & _ 
            "on multi-area selections" 
    End If 
End Sub

此示例使用 Range 对象的 AdvancedFilter 方法在 A 列的区域中创建一个唯一值列表和这些唯一值的出现次数。This example uses the AdvancedFilter method of the Range object to create a list of the unique values, and the number of times those unique values occur, in the range of column A.

Sub Create_Unique_List_Count()
    'Excel workbook, the source and target worksheets, and the source and target ranges.
    Dim wbBook As Workbook
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim rnSource As Range
    Dim rnTarget As Range
    Dim rnUnique As Range
    'Variant to hold the unique data
    Dim vaUnique As Variant
    'Number of unique values in the data
    Dim lnCount As Long
    
    'Initialize the Excel objects
    Set wbBook = ThisWorkbook
    With wbBook
        Set wsSource = .Worksheets("Sheet1")
        Set wsTarget = .Worksheets("Sheet2")
    End With
    
    'On the source worksheet, set the range to the data stored in column A
    With wsSource
        Set rnSource = .Range(.Range("A1"), .Range("A100").End(xlDown))
    End With
    
    'On the target worksheet, set the range as column A.
    Set rnTarget = wsTarget.Range("A1")
    
    'Use AdvancedFilter to copy the data from the source to the target,
    'while filtering for duplicate values.
    rnSource.AdvancedFilter Action:=xlFilterCopy, _
                            CopyToRange:=rnTarget, _
                            Unique:=True
                            
    'On the target worksheet, set the unique range on Column A, excluding the first cell
    '(which will contain the "List" header for the column).
    With wsTarget
        Set rnUnique = .Range(.Range("A2"), .Range("A100").End(xlUp))
    End With
    
    'Assign all the values of the Unique range into the Unique variant.
    vaUnique = rnUnique.Value
    
    'Count the number of occurrences of every unique value in the source data,
    'and list it next to its relevant value.
    For lnCount = 1 To UBound(vaUnique)
        rnUnique(lnCount, 1).Offset(0, 1).Value = _
            Application.Evaluate("COUNTIF(" & _
            rnSource.Address(External:=True) & _
            ",""" & rnUnique(lnCount, 1).Text & """)")
    Next lnCount
    
    'Label the column of occurrences with "Occurrences"
    With rnTarget.Offset(0, 1)
        .Value = "Occurrences"
        .Font.Bold = True
    End With

End Sub

方法Methods

属性Properties

另请参阅See also

支持和反馈Support and feedback

有关于 Office VBA 或本文档的疑问或反馈?Have questions or feedback about Office VBA or this documentation? 请参阅 Office VBA 支持和反馈,获取有关如何接收支持和提供反馈的指南。Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.