Excel VBA Range对象基本操作应用示例

2011-03-02 11:41:43|  分类： Excel 技巧 |  标签： |举报 |字号大中小 订阅 

[示例01] 赋值给某单元格

[示例01-01] 

Sub test1()

Worksheets("Sheet1").Range("A5").Value = 22

MsgBox "工作表Sheet1内单元格A5中的值为" _

& Worksheets("Sheet1").Range("A5").Value

End Sub

[示例01-02]

Sub test2()

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

Worksheets("Sheet1").Range("A5").Value

MsgBox "现在A1单元格中的值也为" & _

Worksheets("Sheet1").Range("A5").Value

End Sub

[示例01-03]

Sub test3()

MsgBox "用公式填充单元格,本例为随机数公式"

Range("A1:H8").Formula = "=Rand()"

End Sub

[示例01-04]

Sub test4()

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

MsgBox "现在单元格A1的值为24"

End Sub

[示例01-05]

Sub test5()

MsgBox "给单元格设置公式,求B2至B5单元格区域之和"

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

End Sub

[示例01-06]

Sub test6()

MsgBox "设置单元格C5中的公式."

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

End Sub

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

[示例02] 引用单元格

Sub Random()

Dim myRange As Range

'设置对单元格区域的引用

Set myRange = Worksheets("Sheet1").Range("A1:D5")

'对Range对象进行操作

myRange.Formula = "=RAND()"

myRange.Font.Bold = True

End Sub

示例说明：可以设置Range对象变量来引用单元格区域，然后对该变量所代表的单元格区域进行操作。

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

[示例03] 清除单元格

[示例03-01]清除单元格中的内容(ClearContents方法)

Sub testClearContents()

MsgBox "清除指定单元格区域中的内容"

Worksheets(1).Range("A1:H8").ClearContents

End Sub

[示例03-02]清除单元格中的格式(ClearFormats方法)

Sub testClearFormats()

MsgBox "清除指定单元格区域中的格式"

Worksheets(1).Range("A1:H8").ClearFormats

End Sub

[示例03-03]清除单元格中的批注(ClearComments方法)

Sub testClearComments()

MsgBox "清除指定单元格区域中的批注"

Worksheets(1).Range("A1:H8").ClearComments

End Sub

[示例03-04]清除单元格中的全部，包括内容、格式和批注(Clear方法)

Sub testClear()

MsgBox "彻底清除指定单元格区域"

Worksheets(1).Range("A1:H8").Clear

End Sub

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

[示例04] Range和Cells

Sub test()

'设置单元格区域A1:J10的边框线条样式

With Worksheets(1)

.Range(.Cells(1, 1), _

.Cells(10, 10)).Borders.LineStyle = xlThick

End With

End Sub

示例说明：可用 Range(cell1, cell2) 返回一个 Range 对象，其中cell1和cell2为指定起始和终止位置的Range对象。

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

[示例05] 选取单元格区域(Select方法)

Sub testSelect()

'选取单元格区域A1:D5

Worksheets("Sheet1").Range("A1:D5").Select

End Sub

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

[示例06] 基于所选区域偏离至另一区域(Offset属性)

[示例06-01]

Sub testOffset()

Worksheets("Sheet1").Activate

Selection.Offset(3, 1).Select

End Sub

示例说明：可用Offset(row, column)(其中row和column为行偏移量和列偏移量)返回相对于另一区域在指定偏移量处的区域。如上例选定位于当前选定区域左上角单元格的向下三行且向右一列处单元格区域。

[示例06-02] 选取距当前单元格指定行数和列数的单元格

Sub ActiveCellOffice()

MsgBox "显示距当前单元格第3列、第2行的单元格中的值"

MsgBox ActiveCell.Offset(3, 2).Value

End Sub

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

[示例07] 调整区域的大小(Resize属性)

Sub ResizeRange()

Dim numRows As Integer, numcolumns As Integer

Worksheets("Sheet1").Activate

numRows = Selection.Rows.Count

numcolumns = Selection.Columns.Count

Selection.Resize(numRows + 1, numcolumns + 1).Select

End Sub

示例说明：本示例调整所选区域的大小，使之增加一行一列。

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

[示例08] 选取多个区域(Union方法)

Sub testUnion()

Dim rng1 As Range, rng2 As Range, myMultiAreaRange As Range

Worksheets("sheet1").Activate

Set rng1 = Range("A1:B2")

Set rng2 = Range("C3:D4")

Set myMultiAreaRange = Union(rng1, rng2)

myMultiAreaRange.Select

End Sub

示例说明：可用 Union(range1, range2, ...) 返回多块区域，即该区域由两个或多个连续的单元格区域所组成。如上例创建由单元格区域A1:B2和C3:D4组合定义的对象，然后选定该定义区域。

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

[示例09] 激活已选区域中的单元格

Sub ActivateRange()

MsgBox "选取单元格区域B2:D6并将C4选中"

ActiveSheet.Range("B3:D6").Select

Range("C5").Activate

End Sub

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

[示例10] 选取指定条件的单元格(SpecialCells方法)

Sub SelectSpecialCells()

MsgBox "选择当前工作表中所有公式单元格"

ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas).Select

End Sub

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

[示例11] 选取矩形区域(CurrentRegion属性)

'选取包含当前单元格的矩形区域

'该区域周边为空白行和空白列

Sub SelectCurrentRegion()

MsgBox "选取包含当前单元格的矩形区域"

ActiveCell.CurrentRegion.Select

End Sub

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

[示例12] 选取当前工作表中已用单元格(UsedRange属性)

'选取当前工作表中已使用的单元格区域

Sub SelectUsedRange()

MsgBox "选取当前工作表中已使用的单元格区域" _

& vbCrLf & "并显示其地址"

ActiveSheet.UsedRange.Select

MsgBox ActiveSheet.UsedRange.Address

End Sub

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

[示例13] 选取最边缘单元格(End属性)

'选取最下方的单元格

Sub SelectEndCell()

MsgBox "选取当前单元格区域内最下方的单元格"

ActiveCell.End(xlDown).Select

End Sub

示例说明：可以改变参数xlDown以选取最左边、最右边、最上方的单元格。

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

[示例14]设置当前单元格的前一个单元格和后一个单元格的值

Sub SetCellValue()

MsgBox "将当前单元格中前面的单元格值设为""我前面的单元格""" & vbCrLf _

& "后面的单元格值设为""我后面的单元格"""

ActiveCell.Previous.Value = "我前面的单元格"

ActiveCell.Next.Value = "我后面的单元格"

End Sub

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

[示例15]确认所选单元格区域中是否有公式(HasFormula属性)

Sub IfHasFormula()

If Selection.HasFormula = True Then

MsgBox "所选单元格中都有公式"

Else

MsgBox "所选单元格中，部分单元格没有公式"

End If

End Sub

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

[示例16] 公式单元格操作

[示例16-01]获取与运算结果单元格有直接关系的单元格

Sub CalRelationCell()

MsgBox "选取与当前单元格的计算结果相关的单元格"

ActiveCell.DirectPrecedents.Select

End Sub

[示例16-02]追踪公式单元格

Sub Cal1()

MsgBox "选取计算结果单元格相关的所有单元格"

ActiveCell.Precedents.Select

End Sub

Sub TrackCell()

MsgBox "追踪运算结果单元格"

ActiveCell.ShowPrecedents

End Sub

Sub DelTrack()

MsgBox "删除追踪线"

ActiveCell.ShowPrecedents Remove:=True

End Sub

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

[示例17] 复制单元格(Copy方法)

Sub CopyRange()

MsgBox "在单元格B7中写入公式后,将B7的內容复制到C7:D7內"

Range("B7").Formula = "=Sum(B3:B6)"

Range("B7").Copy Destination:=Range("C7:D7")

End Sub

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

[示例18]获取单元格行列值(Row属性和Column属性)

Sub RangePosition()

MsgBox "显示所选单元格区域的行列值"

MsgBox "第 " & Selection.Row & "行 " & Selection.Column & "列"

End Sub

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

[示例19]获取单元格区域的单元格数及行列数(Rows属性、Columns属性和Count属性)

Sub GetRowColumnNum()

MsgBox "显示所选取单元格区域的单元格数、行数和列数"

MsgBox "单元格区域中的单元格数为:" & Selection.Count

MsgBox "单元格区域中的行数为:" & Selection.Rows.Count

MsgBox "单元格区域中的列数为:" & Selection.Columns.Count

End Sub

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

[示例20]设置单元格中的文本格式

[示例20-01] 对齐文本

Sub HorizontalAlign()

MsgBox "将所选单元格区域中的文本左右对齐方式设为居中"

Selection.HorizontalAlignment = xlHAlignCenter

End Sub

Sub VerticalAlign()

MsgBox "将所选单元格区域中的文本上下对齐方式设为居中"

Selection.RowHeight = 36

Selection.VerticalAlignment = xlVAlignCenter

End Sub

[示例20-02] 缩排文本(InsertIndent方法)

Sub Indent()

MsgBox "将所选单元格区域中的文本缩排值加1"

Selection.InsertIndent 1

MsgBox "将缩排值恢复"

Selection.InsertIndent -1

End Sub

[示例20-03] 设置文本方向(Orientation属性)

Sub ChangeOrientation()

MsgBox "将所选单元格中的文本顺时针旋转45度"

Selection.Orientation = 45

MsgBox "将文本由横向改为纵向"

Selection.Orientation = xlVertical

MsgBox "将文本方向恢复原值"

Selection.Orientation = xlHorizontal

End Sub

[示例20-04]自动换行(WrapText属性)

Sub ChangeRow()

Dim i

MsgBox "将所选单元格设置为自动换行"

i = Selection.WrapText

Selection.WrapText = True

MsgBox "恢复原状"

Selection.WrapText = i

End Sub

[示例20-05]将比单元格列宽长的文本缩小到能容纳列宽大小(ShrinkToFit属性)

Sub AutoFit()

Dim i

MsgBox "将长于列宽的文本缩到与列宽相同"

i = Selection.ShrinkToFit

Selection.ShrinkToFit = True

MsgBox "恢复原状"

Selection.ShrinkToFit = i

End Sub

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

[示例21]设置条件格式(FormatConditions属性)

Sub FormatConditions()

MsgBox "在所选单元格区域中将单元格值小于10的单元格中的文本变为红色"

Selection.FormatConditions.Add Type:=xlCellValue, _

Operator:=xlLessEqual, Formula1:="10"

Selection.FormatConditions(1).Font.ColorIndex = 3

MsgBox "恢复原状"

Selection.FormatConditions(1).Font.ColorIndex = xlAutomatic

End Sub

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

[示例22]插入批注(AddComment方法)

Sub EnterComment()

MsgBox "在当前单元格中输入批注"

ActiveCell.AddComment ("Hello")

ActiveCell.Comment.Visible = True

End Sub

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

[示例23]隐藏/显示单元格批注

Sub CellComment()

MsgBox "切换当前单元格批注的显示和隐藏状态"

ActiveCell.Comment.Visible = Not (ActiveCell.Comment.Visible)

End Sub

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

[示例24]改变所选单元格的颜色

Sub ChangeColor()

Dim iro As Integer

MsgBox "将所选单元格的颜色改为红色"

iro = Selection.Interior.ColorIndex

Selection.Interior.ColorIndex = 3

MsgBox "将所选单元格的颜色改为蓝色"

Selection.Interior.Color = RGB(0, 0, 255)

MsgBox "恢复原状"

Selection.Interior.ColorIndex = iro

End Sub

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

[示例25]改变单元格的图案

Sub ChangePattern()

Dim p, pc, i

MsgBox "依Pattern常数值的顺序改变所选单元格的图案"

p = Selection.Interior.Pattern

pc = Selection.Interior.PatternColorIndex

For i = 9 To 16

With Selection.Interior

.Pattern = i

.PatternColor = RGB(255, 0, 0)

End With

MsgBox "常数值 " & i

Next i

MsgBox "恢复原状"

Selection.Interior.Pattern = p

Selection.Interior.PatternColorIndex = pc

End Sub

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

[示例26]合并单元格

Sub MergeCells()

MsgBox "合并单元格A2:C2,并将文本设为居中对齐"

Range("A2:C2").Select

With Selection

.MergeCells = True

.HorizontalAlignment = xlCenter

End With

End Sub

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

[示例27]单元格移动的范围

Sub ScrollArea1()

MsgBox "将单元格的移动范围在单元格区域B2:D6中"

ActiveSheet.ScrollArea = "B2:D6"

End Sub

Sub ScrollArea2()

MsgBox "解除移动范围"

ActiveSheet.ScrollArea = ""

End Sub

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

[示例28]获取单元格的位置(Address属性)

Sub GetAddress()

MsgBox "显示所选单元格区域的地址"

MsgBox "绝对地址:" & Selection.Address

MsgBox "行的绝对地址:" & Selection.Address(RowAbsolute:=False)

MsgBox "列的绝对地址:" & Selection.Address(ColumnAbsolute:=False)

MsgBox "以R1C1形式显示:" & Selection.Address(ReferenceStyle:=xlR1C1)

MsgBox "相对地址:" & Selection.Address(False, False)

End Sub

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

[示例29]删除单元格区域(Delete方法)

Sub DeleteRange()

MsgBox "删除单元格区域C2:D6后,右侧的单元格向左移动"

ActiveSheet.Range("C2:D6").Delete (xlShiftToLeft)

End Sub

小结

下面对Range对象的一些常用属性和方法进行简单的小结。

1、Activate与Select

试验下面的过程：

Sub SelectAndActivate()

Range("B3:E10").Select

Range("C5").Activate

End Sub

其结果如下图所示：

图05-01：Select与Activate

Selection指单元格区域B3：E10，而ActiveCell则是单元格C5；ActiveCell代表单个的单元格，而Selection则可以代表单个单元格，也可以代表单元格区域。

2、Range属性

可以使用Application对象的Range属性引用Range对象，如

Application.Range(“B2”) ‘代表当前工作表中的单元格B2

若引用当前工作表中的单元格，也可以忽略前面的Application对象。

Range(“A1:D10”) ‘代表当前工作表中的单元格区域A1:D10

Range(“A1:A10,C1:C10,E1:E10”) ‘代表当前工作表中非连续的三个区域组成的单元格区域

Range属性也接受指向单元格区域对角的两个参数，如：

Range(“A1”,”D10”) ‘代表单元格区域A1：D10

当然，Range属性也接受单元格区域名称，如：

Range(“Data”) ‘代表名为Data的数据区域

Range属性的参数可以是对象也可以是字符串，如：

Range(“A1”,Range(“LastCell”))

3、单元格引用的快捷方式

可以在引用区域两侧加上方括号来快速引用单元格区域，如：

[B2]

[A1:D10]

[A1:A10,C1:C10,E1:E10]

[Data]

但其引用的是绝对区域。

4、Cells属性

可以使用Cells属性来引用Range对象。如：

ActiveSheet.Cells

Application.Cells ‘引用当前工作表中的所有单元格

Cell(2,2)

Cell(2,”B”) ‘引用单元格B2

Range(Cells(1,1),Cells(10,5)) ‘引用单元格区域A1:E10

若想在一个单元格区域中循环时，使用Cells属性是很方便的。

也可以使用Cells属性进行相对引用，如：

Range(“D10:G20”).Cells(2,3) ‘表示引用单元格区域D10:G20中第2行第3列的单元格，即单元格F11

也可使用语句：Range(“D10”).Cells(2,3)达到同样的引用效果。

5、Offset属性

Offset属性基于当前单元格按所给参数进行偏移，与Cells属性不同的是，它基于0即基准单元格为0，如：

Range(“A10”).Cells(1,1)和Range(“A10”).Offset(0,0)都表示单元格A10

当想引用于基准单元格区域同样大小的单元格区域时，则Offset属性是有用的。

6、Resize属性

可使用Resize属性获取相对于原单元格区域左上角单元格指定大小的区域。

7、SpecialCells方法

SpecialCells方法对应于“定位条件”对话框，如图05-02所示：

图05-02：“定位条件”对话框

8、CurrentRegion属性

使用CurrentRegion属性可以选取当前单元格所在区域，即周围是空行和空列所围成的矩形区域，等价于“Ctrl+Shift+*”快捷键。

9、End属性

End属性所代表的操作等价于“Ctrl+方向箭”的操作，使用常量xlUp、xlDown、xlToLeft和xlToRight分别代表上、下、左、右箭。

10、Columns属性和Rows属性

Columns属性和Rows属性分别返回单元格区域中的所有列和所有行。

11、Areas集合

在多个非连续的单元格区域中使用Columns属性和Rows属性时，只是返回第一个区域的行或列，如：

Range(“A1:B5,C6:D10,E11:F15”).Rows.Count

将返回5。

此时应使用Areas集合来返回区域中每个块的地址，如：

For Each Rng In Range(“A1:B5,C6:D10,E11:F15”).Areas

MsgBox Rng.Address

Next Rng

12、Union方法和Intersect方法

当想从两个或多个单元格区域中生成一个单元格区域时，使用Union方法；当找到两个或多个单元格区域共同拥有的单元格区域时，使用Intersect方法。