VBA基础教程Day03： 单元格区域操作01

3-0.EXCEL单元格基础

单元格：是excel中最小的一个单位（工作簿，工作表，行，列，单元格）

单元格也是我们操作得最多的。

单元格行数：1048576

单元格列数：16384

单元格个数：17179869184

单元格容量：32767 个字符

 

VBA中单元格学习的重要性：

在后续的章节里：因为在部分的工作是在操作单元格。

我们一直离不到单元格的表示方法与操作。

那么关于单元格的点点滴滴的信息在VBA中的表示方法就显得非常的重要

3-1.Range引用基础1

单元格对象在VBA中一个非常基础，同时也很重要的。

它的表达方式也是非常的多样化。

Range 对象

代表某一单元格、某一行、某一列、某一选定区域（该区域可包含一个或若干连续单元格区域），或者某一三维区域。

Range ("文本型装单元格地址")

range的常见写法

?

1 2 3 4 5 6 7 8 9 10 11 12 13

Sub rng()   Range("a1").Select         '单元格   Range("a:a").Select        '列   Range("1:3").Select       '行   Range("a1:b10").Select     '相邻区域   Range("a1:d7,c4:e8").Select  '不相个邻区域   End Sub

3-2.Range引用基础2

写法：Range ("文本型装单元格地址1","文本型装单元格地址2")

range的变化写法

?

1 2 3 4 5 6 7 8 9 10 11

Sub rng变化()   Range("a1:b10").Select '一般写法   Range("a1", "b10").Select '变化写法1   Range(Range("a1"), Range("b10")).Select '变化写法2   Range("a1") = 123   End Sub

注意：

1.如果在range前没有指定工作表，则默认为活动工作表

2.如果对象不是活动工作表（如活动图表），则会出现错误

?

1 2 3 4 5 6 7 8 9 10 11

Sub 单元格对象例子()   Debug.Print Range("a:a").Count '计数工作表最大的行数   Debug.Print Range("1:1").Count '计算工作表最大的列数   Debug.Print Application.CountA(Range("a:a")) '计算工作表已使用的行数   Debug.Print Application.CountA(Range("1:1")) '计算工作表已使用的列数   End Sub

3-3.Range引用与变量

除了上一节的range基本表示方法外，还有更多的变化写法

?

1 2 3 4 5 6 7 8 9

1.range("地址区域").range("地址区域")   Sub 序号表示法()   Range("b2:d4").Range("b2").Select '相对引用的写法   参照前一个range的左上单元格   End Sub

?

1 2 3 4 5 6 7 8 9 10 11 12 13

2.range地址区域中支持变量   Sub range的变量支持()   Dim a%   a = 3   Range("a" & a).Select   Range("c3:e5")(2).Select   End Sub

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

Sub 实例1动态选单元格或区域()   Dim i%   i = Application.CountA(Range("c:c")) '找到c列中已使用的最后一个单元格位置   Range("c" & i).Select '选择C列最后一格   Range("a1", "c" & i).Select '选择A1到C列的最后一格（方法一）   Range("a1:c" & i).Select '选择A1到C列的最后一格（方法二）   小结：动态单元格区域的定位，可以应用到单据的保存等实际工作中   End Sub

3-4.Range引用与索引值

range区域中的每个单元格，我们也可以用索引号表示出来

写法：range("单元格区域")(行号，列号)

?

1 2 3 4 5 6 7 8 9 10 11

Sub 索引号取出range的单元格()   Range("a1:c4")(4).Select '引用顺序是：从左向右，从上到下选取   Range("b2:c4")(3).Select  '以前一个单元格区域为照   Range("a1:c4")(4.5).Select '当有小数时，则取整   注意：如果索引号出现小数，则按照“四舍六入五单双”的“银行家舍入法”   End Sub

?

1 2 3 4 5 6 7

Sub 行列号定位()   Range("a1:c4")(3, 2).Select '利用行号与列号定位   Range("a1:c4")(1.5, 2.5).Select '行列号也可以使用小数   End Sub

小结:

1.这个索引值是参照前一个单元格区域左上单元格进行定位引用的

2.索引值可以是正数，负数，零值，小数

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

Sub 大于等于2500的平增工资()   Dim rs%, rng%, lj&, k%   For rs = 1 To 60       Range("b2:c20")(rs).Select        rng = Range("b2:c20")(rs)       If rng >= 2500 Then lj = lj + rng: k = k + 1   Next rs       MsgBox "大于等于2500的平均分为：" & Int(lj / k)   End Sub

3-5.Range引用与索引值应用（有条件的平均工资）

小结:

1.这个索引值是参照前一个单元格区域左上单元格进行定位引用的

2.索引值可以是正数，负数，零值，小数

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

Sub 大于等于2500的平增工资()   Dim rs%, rng%, lj&, k%   For rs = 1 To 60       Range("b2:c20")(rs).Select        rng = Range("b2:c20")(rs)       If rng >= 2500 Then lj = lj + rng: k = k + 1   Next rs       MsgBox "大于等于2500的平均分为：" & Int(lj / k)   End Sub

3-6.Cells单元格引用基础

cells单元格引用法

写法：cells(行号,列号)

?

1 2 3 4 5 6 7 8 9

Sub cells基本写法()   Cells(3, 4).Select '行列号均为数字   Cells(2, "c").Select '行为数字，列为列标字母   Cells.Select '全选   End Sub

cells可以像range一样可以参照前面的单元格位置

?

1 2 3 4 5 6 7 8 9

Sub 参照写法()   Range("b3:f11").Cells(2, 2).Select   Range("b3:f11").Cells(6).Select '从左到右，从上到下   Range("b3:f11")(6).Select '与上一句相等   End Sub

注意：

1.cells中的数字一样支持正数，负数，0值，小数(四舍六入五单双)

2.cells不能像range一样可以引用一个区域，只能引用一个单元格

cells还可以嵌套在range中

?

1 2 3 4 5 6 7

Sub 嵌套()   Range(Range("b1"), Range("f11")).Select '这种嵌套方法写变量比较麻烦   Range(Cells(3, 2), Cells(11, 6)).Select '这种嵌套方法写变量比较方便   End Sub

?

1 2 3 4 5 6 7 8 9 10 11

Sub 动态引用行列区域()   Dim a%, b%   a = Application.CountA(Range("a:a"))   b = Application.CountA(Range("1:1"))   Range(Cells(1, 1), Cells(a, b)).Select   End Sub

3-7.单元格简写

除了前面讲的range\cells单元格区域的表示方法，还有一种简单的写法

写法: [单元格地址] '注意：中括号中的单元格地址并不需要双引号("")

?

1 2 3 4 5 6 7 8 9 10 11 12 13

Sub 单元格简写()   [a3].Select ' 单元格引用   [b2:c6].Select '单元格区域引用   [a3,b2:c6,b8:d12].Select '多区域引用   [a:a].Select '整列引用   [1:1].Select '整行引用   End Sub

单元格简写的也支持引用子集

?

1 2 3 4 5 6 7 8 9

Sub 子集引用()   [b2:c6].Item(3).Select   Range("b2:c6")(3).Select   [b2:c6].Cells(4).Select   End Sub

?

1 2 3 4 5 6 7 8 9

Sub 动态区域的引用()   a = Application.CountA([a:a])   b = Application.CountA([1:1])   Range(Range("a1"), Range(Chr(64 + b) & a)).Select '利用chr函数，让字母形式的列号也支持变量   End Sub

?

1 2 3 4 5 6 7 8 9 10 11

Sub chr函数字符循环()   For i = 1 To 65535   Cells(i, 1) = i   Cells(i, 2) = Chr(i)   Next   End Sub

3-8.三种单元格引用小结

?

1 2 3 4 5 6 7 8 9 10 11 12 13

Sub range引用区域且有变量()   Dim i   i = 1   Range("a1:c" & i).Select '引用单元格是区域且有变量   Cells(i, "c").Select '引用的是单个单元格且有变量   [a1:19].Select '引用的是区域或单元格且无变量   End Sub

3-9. 行、列引用

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73

行列引用   Sub 列引用()   Columns(1).Select   Columns("b").Select   Columns("c:e").Select   End Sub      Sub 行引用()   Rows(1).Select   Rows("2").Select   Rows("3:4").Select   End Sub     Sub range行列表式法()   Range("1:1").Select   Range("2:4").Select   Range("a:a").Select   Range("b:d").Select   End Sub   Sub 简写法()   [a:a].Select   [b:d].Select   [1:1].Select   [2:4].Select   End Sub   Sub 全选()   Rows.Select '选择所有行   Columns.Select '选择所有列   Cells.Select '选择所单元格   i = Rows.Count   j = Columns.Count   k = Cells.Count   End Sub   Sub 动态引用使用区域()   a = Application.CountA(Columns(1))   b = Application.CountA(Rows(1))   Range("a1", Cells(a, b)).Select   End Sub

3-10.row与column属性

Range.Row 属性

返回区域中第一个子区域的第一行的行号

Range.Column 属性

返回指定区域中第一块中的第一列的列号

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

Sub test()   i = Range("a3:b9").Range("a5").Row   j = Range("a3:b9").Row   i = Range("b3:d9").Range("a5").Column   j = Range("b3:d9").Column   End Sub     Sub row应用()   For Each rw In Rows("1:13")       If rw.Row Mod 2 = 0 Then           rw.RowHeight = 5       End If   Next rw   End Sub

3-11.单元格的值与地址

单元格的值表示方法

?

1 2 3 4 5 6 7 8 9

Sub 单元格值表示()   a = [a1].Value  '实际是什么，就是什么   b = [a1].Text    '看到是什么，就是什么   c = [a1]   End Sub

注意：一个单元格可以省略value，多单元格区域不能省略

?

1 2 3 4 5

Sub 多区域赋值()   Range("e1:e4") = Range("d1:d4").Value   End Sub

单元格地址与引用

?

1 2 3 4 5 6 7 8 9 10 11 12 13

Sub 地址与引用()   Set rng = [b2:f2]   [a9] = rng.Address(1, 1)  '绝对引用   [b9] = rng.Address(0, 0)  '相对引用   [c9] = rng.Address(1, 0)   '混合引用   [d9] = rng.Address(0, 1)   '混合引用   End Sub

'总结：1代表固定，0代表不固定,默认是绝对引用

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

Sub 地址引用实例()   '将表三成绩中为空的单元格标为未考   Dim rng As Range, rn$   On Error Resume Next   For Each rng In Sheet3.Range("b2:d10")       If rng = "" Then rn = rn & rng.Address & ","   Next   Range(Left(rn, Len(rn) - 1)) = "未考"   End Sub

思考题:用上题的思路，将成绩中的"未考"去掉为空?

3-12.移动与复制

1.Range.Cut 方法

    '将单元格区域剪切到指定的区域

2.Range.Copy 方法

    '将单元格区域复制到指定的区域

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

Sub 移动复制()   Range("a1:d8").Cut Range("f1")   Range("f1:i8").Copy Range("a1")   End Sub     Sub 另类复制方法()   Range("a10:d17") = Range("a1:d8").Value   End Sub

'注：

'1.等号后的区域一定要加value.否则不成功

'2.被赋值的区域格式全部去掉

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

Sub 出差登记表记录保存()   Dim rs%, crs%   rs = Application.CountA([d:d])   crs = Application.CountA(Sheets("出差记录表").[a:a]) + 1   If rs = 1 Then GoTo 100   Range("a2:d" & rs).Copy Sheets("出差记录表").Range("a" & crs)   End   100:   MsgBox "没有要保存的数据！"   End Sub

3-13.插入与删除

工作表中单元格，行与列的插入与删除

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

Sub 插入()   Rows(2).Insert   End Sub   Sub 隔行插入()   Dim r%       Do           r = r + 2           Rows(r).Insert       Loop Until Cells(r + 1, 1) = ""   End Sub   Sub 删除()   Rows(1).Delete   End Sub   Sub 隔行删除()   Dim r, s   m = Application.CountA(Columns(1))       For r = 1 To m / 2           Rows(r).Delete       Next   End Sub

3-14.activeCell与selection

活动单元格：activecell,工作表中活动单元格只有一个

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41

Sub activecells()   a = activecell.Address  '取得活动单元格地址   Cells(2, 3).Activate    '激活指定单元格   End Sub   selection光标所选区域   Sub 光标所选区域()       Selection = 1   End Sub   Sub 在selection中的改变活单元格()   For i = 1 To Selection.Count       Selection(i).Activate   Next   End Sub   Sub 运用()   Dim i As Range   For Each i In Selection       If i = "" Or i = "缺勤" Then           i = "×"       End If   Next i   End Sub

小结：selection的好处在于，可以很自由灵活选择你想要处理的单元格区域

3-15.UsedRange已使用区域

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

Option Explicit   Sub 宏3()       Range("C4").Select       Selection.CurrentRegion.Select   End Sub   Sub 宏4()       Range("G6").Select   End Sub

3-16.currentregion当前区域

?

1 2 3 4 5 6 7 8 9 10 11

Range.CurrentRegion 属性   返回一个 Range 对象，该对象表示当前区域。   Sub 当前区域()       [a1].CurrentRegion.Select       [f8].CurrentRegion.Select   End Sub

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

Sub currentregion应用()       Rows(8).Clear       a = [b2].CurrentRegion.Address       b = [b5].CurrentRegion.Address       c = [b2].CurrentRegion.Count + 1       Set c = Range("b8", Cells(8, c))       c.FormulaArray = "=" & a & "+" & b   End Sub

usedrange与currentregion

如果表中只有一个区域，两者最后的结果是一样的

只是表达方式不一样

?

1 2 3 4 5 6 7

Sub u与c()   Sheet3.UsedRange.Select   [a1].CurrentRegion.Select   End Sub

3-17.单元格的offset

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54

Range.Offset 属性   返回 Range 对象，它代表位于指定单元格区域的一定的偏移量位置上的区域。   表达式.Offset(偏移行, 偏移列)   表达式   一个代表 Range 对象的变量。   偏移行列的数字可以是：正数，负数，零值      Sub test()   [a1].Offset(1, 2).Select    '行列都偏移   [a1].Offset(2).Select       '只偏移行   [a1].Offset(, 2).Select     '只偏移列     如果offset前面的range对象是一个区域，则偏移后也结果尺寸不变   [a1:d1].Offset(1, 2).Select   [a1:d1].Offset(2).Select   [a1:d1].Offset(, 2).Select   End Sub     Sub offset应用1()   Dim i%   For i = 2 To 8 Step 2       [a1:e1].Copy [a1:e1].Offset(i)   Next i   End Sub   Sub offset应用2()   Dim i%   For i = 2 To 8 Step 2       [a1:e1].Offset(i) = ""   Next i   End Sub

3-18.单元格的resize

Range.Resize 属性

调整指定区域的大小。返回 Range 对象，该对象代表调整后的区域。

语法

表达式.Resize(行数, 列数)

表达式   一个返回 Range 对象的表达式。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

Sub test()   [a1].Resize(2, 3).Select   [a1].Resize(2).Select   [a1].Resize(, 3).Select   End Sub   Sub 保存()   Dim i%, j%, k%   i = [a1].CurrentRegion.Rows.Count - 1   j = [a1].CurrentRegion.Columns.Count   k = Application.CountA(Sheet2.Columns(1))   [a2].Resize(i, j).Copy Sheet2.[a1].Offset(k)   End Sub

3-19.单元格所在行与列(删除空行)

Range.EntireRow 属性

返回一个 Range 对象，该对象表示包含指定区域的整行（或多行）。

语法

表达式.EntireRow

表达式   一个代表 Range 对象的变量。

Range.EntireColumn 属性

返回一个 Range 对象，该对象表示包含指定区域的整列（或多列）

语法

表达式.EntireColumn

表达式   一个代表 Range 对象的变量。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

Sub test()   [a1].EntireRow.Select   [a1].EntireColumn.Select   [a1:a4].EntireRow.Select   [a1:d1].EntireColumn.Select   End Sub   Sub test1()   Dim rng As Range, ads As String   For Each rng In [a1:a10]       If rng = "" Then ad = ad & rng.Address & ","   Next   ads = Left(ad, Len(ad) - 1)   Range(ads).EntireRow.Delete   End Sub

3-20.specialcells定位

Range.SpecialCells 方法

返回一个 Range 对象，该对象代表与指定类型和值匹配的所有单元格。

语法

表达式.SpecialCells(Type, Value)

表达式   一个代表 Range 对象的变量。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

Sub 批注汇总()   MsgBox Application.Sum(Selection.SpecialCells(-4144))   End Sub   Sub 删除空行()   On Error GoTo 100   Selection.SpecialCells(xlCellTypeBlanks).Select   Selection.EntireRow.Delete   Exit Sub   100:   MsgBox "没有空行"   End Sub