使用Range对象

引用单元格区域

使用A1引用样式

Range属性的语法格式如下：

Range(cell1,cell2)

可以对指定的区域进行赋值，也可以清空单元格区域

Range("B2").value = "Microsoft Excel 2016"
Range("B2").ClearContents

使用行列编号

在VBA中可使用Cells属性通过行列编号引用单个单元格，Cells属性的语法格式如下。

Cells(RowIndex,ColumnIndex)

1. 两个参数都是可选参数，分别表示行序号与列序号。默认参数的cells属性返回引用对象的所有单元格。

2. 如果仅指定了一个参数，则表示引用单元格的索引号，其顺序为由左至右，再由上至下，逐个索引

Sub CellsIndexDemo()
Dim i As Integer
For i = 1 To Selection.Cells.Count
    Selection.Cells(i) = i
Next i
End Sub

运行代码后，单元格区域内显示为

3. 当指指定ColumnIndex时，则RowIndex的默认值为1。例如Cells(,3)相当于Cells(1,3)

使用快捷记号

将A1引用样式或命名区域名称使用“[ ]”括起来，作为range属性的快捷方式，这样就不必输入关键字“Range"，以实现快捷输入。例如：

dim rng as Range
set rng = [B3:E6]

使用Rows和Columns属性

Rows，Columns属性可以应用于Application对象、Worksheet对象和Range对象，分别返回对象指定区域的所有行和所有列，通过索引号可以返回其中的一行/列或多行/列

Sub selectRows()
    Rows("3:5").Select
End Sub

选择了当前sheet的第3到第5行

Sub selectRows()
    Range("A1:D10").Rows("3:5").Select
End Sub

选择了Range(“A1:D10”)的第3到第5行

使用offset属性

Range对象Offset属性代表位于指定单元格区域的一定偏移量位置上的与指定单元格区域大小相同的单元格区域。Offset属性的语法格式如下：

Offset(RowOffset,ColumnOffset)

使用Resize属性

Range对象Offset属性用于指定区域的大小，并返回调整后的单元格区域，默认使用该区域最左上角单元格作为基准单元格。Resize属性的语法格式如下：

Resize(RowSize,ColumnSize)

其中RowSize,ColumnSize分别代表调整后的单元格区域的行数与列数

使用CurrentRegion属性

Range对象的CurrentRegion属性返回对象所在的当前区域，当前区域是一个边缘为任意空行和空列，或者工作表边缘组成的最小矩形范围。

Sub selectData()
    Dim rng As Range
    Set rng = Range("A1").CurrentRegion
    With rng
    .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Select
    End With
End Sub

使用Areas属性

Sub AreaDemo()
    Dim rng As Range
    Dim i As Byte
    Set rng = Range("A1,B2:D5,G3:I8")
    Debug.Print (rng.Areas.Count)
    For i = 1 To rng.Areas.Count
        MsgBox "将选择第" & i & "个Areas区域:" & _
        rng.Areas(i).Address
        rng.Areas(i).Select
    Next i
    set rng = Nothing
End Sub

Range.Areas属性返回Range本身。对于多重选择区域，Areas返回一个集合，该集合包含Range选择的所有区域。如上面代码中，Areas.Count等于3.Areas(1)为A1Areas(2)为B2:D5,Areas(3)为G3:I8

取得最后一个非空单元格

获得指定行/列的最后一个非空单元格，一般需要使用Range对象的End属性

其语法是：

end(xlDirection)

xlDirection 常量

常量	说明
xlDown	向下
xlToRight	向右
xlToLeft	向左
xlUp	向上

复制单元格区域

使用Range的Copy方法，可以复制指定单元格区域到另一个单元格区域。如下代码

Sub RangeCopy()
    Dim rng As Range
    Set rng = Range("A1").CurrentRegion
    rng.Copy Destination:=Worksheets("Sheet2").[a1]
End Sub

Range.Copy的语法格式如下

Range.Copy(Destination)

参数Destination如果省略，Excel会把该区域复制到剪贴板

复制单元格区域的操作不会将源单元格区域列宽复制到目标区域。如下示例代码可以在实现复制单元格区域时，使目标单元格区域的列宽与源区域保持一致。

Sub CopyWithSameColumnWidth()
    Sheets("Sheet1").Range("A1").CurrentRegion.Copy
    With Worksheets("Sheet2").Range("A1")
        .PasteSpecial xlPasteColumnWidths
        .PasteSpecial xlPasteAll
    End With
    Application.CutCopyMode = False
End Sub

Range对象的PasteSpecial方法将剪贴板中的Range对象粘贴到指定区域，在粘贴时可以有选择地粘贴对象的部分属性，其语法格式如下。

PasteSpecial(Paste,Operation,SkipBlanks,Transpose)

具体的其他属性，可以使用录制宏的方式查看。

仅复制数值到另一个区域

使用仅粘贴数值的方式

直接赋值

对行进行快速分组

获取两个单元格区域的交叉区域

使用Application.Intersect方法可以获得两个单元格区域的交叉区域

语法：

Application.Intersect(range1,range2[, ... ...])

联合多个单元格区域

Application.Union方法可以将多个单元格区域联合为同一个Range对象，语法如下

Application.Union(range1,range2[, ... ...])

判断一个区域是否包含在另一个区域

利用Application.Intersect方法

Function blnInclude_Intersect(ByVal Rngcell1 As Range, ByVal Rngcell2 As Range)
    Dim rngIntersect As Range
    Set rngIntersect = Application.Intersect(Rngcell1, Rngcell2)
    If Not rngIntersect Is Nothing Then
        If rngIntersect.Address = Rngcell1.Address Then
        blnInclude_Intersect = False
        End If
    End If
    Set rngIntersect = Nothing
End Function

利用Application.Union方法

Function blnInclude_Union(ByVal rngcell1 As Range, ByVal rngcell2 As Range) As Boolean
    Dim rngUnion As Range
    Set rngUnion = Application.Union(rngcell1, rngcell2)
    If rngUnion.Address = rngcell2.Address Then blnclude_Union = True
End Function

设置字符格式

设置单元格文本字符格式

如下示例为A1单元格写入文本，并设置单元格内的字符格式

Sub CellCharacter()
    With Range("A1")
        .Clear
        .Value = "Y=X2+2"
        .Characters(4, 1).Font.Superscript = True
        .Characters(1, 1).Font.ColorIndex = 3
        .Font.Size = 20
    End With
End Sub

• Characters属性返回一个Characters对象，它可以理解为一个单元格中字符串的子集，本身来讲也是字符串

设置注释

如下示例为A3单元格设置注释

Sub ShapeChatacters()
If Range("A3").Comment Is Nothing Then
    Range("A3").AddComment Text:=""
End If
With Range("A3").Comment
	'注释框始终可见'
    .Visible = True
	'设置注释内容'
    .Text Text:="Microsoft Excel 2016"
	'设置注释的内容，详见下方代码详解'
    .Shape.TextFrame.Characters.Font.ColorIndex = 3
End With
End Sub

代码详解：

• 注释的本质其实就是一个图形（Shape)，因此他有Comment.Shape是一个Shape对象，Shape对象中存在各种属性，具体的请详见后面的使用Shape对象

单元格区域添加边框

绘制统一的边框样式

使用Range对象的Borders集合可以快速对单元格区域全部边框应用想用的格式，而Range对象的BorderAround方法可以快速的为单元格区域添加外边框。参照如下代码

Sub AddBorders()
    Dim rng As Range
    Set rng = Range("B2:F8")
    With rng.Borders
		'设定线条的类型为实线'
        .LineStyle = xlContinuous
		'设定边框的粗细'
        .Weight = xlThin
		'设定线条的颜色'
        .ColorIndex = 5
    End With
    '设定外边框'
    rng.BorderAround xlContinuous, xlMedium, 5	'这是不具名参数的写法'
    Set rng = Nothing
End Sub

• 不具名参数：

  在VBA中，当调用一个方法或函数时，你可以通过参数名称来明确地为每个参数赋值（即使用具名参数），也可以不指定参数名称，按照方法或函数定义中的参数顺序来提供值（即使用位置参数）。但是在使用位置参数时，必须确保参数值的顺序与方法或函数定义的参数顺序完全一致。

按照水平与垂直绘制不同的表格样式

我们在上一段代码的基础上进行修改

Sub AddBorders()
    Dim rng As Range
    Set rng = Range("B2:F8")
    With rng.Borders(xlInsideHorizontal)
        .LineStyle = xlDot
        .Weight = xlThin
        .ColorIndex = 5
    End With
    With rng.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 5
    End With
    rng.BorderAround xlContinuous, xlMedium, 5
    Set rng = Nothing
End Sub

Range.Borders(index)中index的取值列表，在excel对象浏览器中搜索xlBordersIndex，均有说明

常量	引用
xlDiagonalDown	斜下边框
xlDiagonalUp	斜上边框
xlEdgeBottom	底部边框
xlEdgeTop	上部边框
xlEdgeLeft	左边框
xlEdgeRight	右边框
xlInsideHorizontal	内部水平
xlInsideVertical	内部垂直

设置区域内单元格选择高亮

Option Explicit
Dim HighLight As Range
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Set HighLight = Range("B2:H15")
    Dim rngHighLight As Range
    Dim rng1 As Range
    Dim rng2 As Range
    '取消高亮显示区域内的填充颜色'
    HighLight.Interior.ColorIndex = xlNone
    Set rng1 = Application.Intersect(Target.EntireColumn, HighLight)
    Set rng2 = Application.Intersect(Target.EntireRow, HighLight)
    On Error Resume Next
    Set rngHighLight = Application.Union(rng1, rng2)
    rngHighLight.Interior.ThemeColor = 9
End Sub

• 补充：VBA提供选择整行整列的代码，分别为Range.EntireRow、Range.EntireColumn

动态设置单元格数据验证序列

它是Range的Validation对象。推荐使用宏方式，再调试代码。

将单元格公式转换为数值

可以使用选择性粘贴，赋值等方法

判断单元格公式是否存在错误

语法如下：

VBA.IsError(range.value)

例如：单元格B5=0/0，这显然是一个错误，使用如下代码进行判断

Sub FormulaIsError()
    If VBA.IsError(Range("B5")) Then
        MsgBox "B5单元格错误类型为" & [B5].Text
    End If
End Sub

批量删除所有错误值

首先，我们可以使用循环遍历的方式，判断每一个单元格是否是错误值。但如果需要判断的区域过大，那我们就需要使用定位条件的方式将错误值先初步筛选出来，再进行遍历删除。

定位条件的VBA是Range的SpecialCells方法

定位条件的VBA是Range的SpecialCells方法

Sub DeleteAllError()
Dim rng As Range
On Error Resume Next
Set rng = Range("D11").CurrentRegion.SpecialCells(xlCellTypeFormulas, xlErrors)

If Err.Number = 1004 Then
    MsgBox "未找到单元格"
Else
    rng.ClearContents
End If

End Sub

SpecialCells的语法如下

SpecialCells(type,value)

注意：如果选择注释等没有单元格数值的常量，第二个参数不需要填写

XlCellType与Xlspecialcellsvalue可参照对象浏览器与宏

返回指定列的列标

使用VBA.Split将Address进行分隔，它会返回一个数组

Function strGetColumnLetter(ByVal intcol As Integer) As String
    Debug.Print (Cells(1, intcol).Address)
    Dim temp
    temp = VBA.Split(Cells(1, intcol).Address, "$")
    strGetColumnLetter = VBA.Split(Cells(1, intcol).Address, "$")(1)
End Function
Sub test()
    strGetColumnLetter (2)
End Sub

快速判断单元格区域是否存在合并单元格

使用Range对象的MergeCells属性.如果区域内包含合并单元格，此属性的值为 True。

Sub IsMergeCell()
    If Range("A1").MergeCells = True Then
        MsgBox "包含合并单元格"
    Else
        MsgBox "未包含合并单元格"
    End If
End Sub

合并单元格时连接每个单元格内容

合并单元格的方法是Range.Merge

取消合并时再每个单元格中保留内容

取消合并单元格的方式是Range.UnMerge