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
Use_expression_.Cells,其中 expression 表达式将返回一个 Range 对象,以获取由单个单元格组成的相同地址的区域。
在此区域中,你可以通过 Item(row, column) 访问单个单元格,此位置相对于该区域的第一个区的左上角。
Item 可以省略,因为 Range 的默认成员会将调用转发给它。
以下示例设置活动工作簿第一个工作表单元格 C5 和 D5 的公式。
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 属性很有用。 它会将所选定的多区域范围划分为单个 Range 对象,然后以集合的形式返回对象。 可对所返回的集合使用 Count 属性,以验证包含多个区域的选定范围(如下例所示)。
VB
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 列的区域中创建一个唯一值列表和这些唯一值的出现次数。
VB
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