3-0.EXCEL单元格基础
单元格:是excel中最小的一个单位(工作簿,工作表,行,列,单元格)
单元格也是我们操作得最多的。
单元格行数:1048576
单元格列数:16384
单元格个数:17179869184
单元格容量:32767 个字符
VBA中单元格学习的重要性:
在后续的章节里:因为在部分的工作是在操作单元格。
我们一直离不到单元格的表示方法与操作。
那么关于单元格的点点滴滴的信息在VBA中的表示方法就显得非常的重要
3-1.Range引用基础1
单元格对象在VBA中一个非常基础,同时也很重要的。
它的表达方式也是非常的多样化。
Range 对象
代表某一单元格、某一行、某一列、某一选定区域(该区域可包含一个或若干连续单元格区域),或者某一三维区域。
Range ("文本型装单元格地址")
range的常见写法
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的变化写法
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.如果对象不是活动工作表(如活动图表),则会出现错误
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.range("地址区域").range("地址区域")
Sub 序号表示法()
Range("b2:d4").Range("b2").Select '相对引用的写法
参照前一个range的左上单元格
End Sub
2.range地址区域中支持变量
Sub range的变量支持()
Dim a%
a = 3
Range("a" & a).Select
Range("c3:e5")(2).Select
End Sub
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("单元格区域")(行号,列号)
Sub 索引号取出range的单元格()
Range("a1:c4")(4).Select '引用顺序是:从左向右,从上到下选取
Range("b2:c4")(3).Select '以前一个单元格区域为照
Range("a1:c4")(4.5).Select '当有小数时,则取整
注意:如果索引号出现小数,则按照“四舍六入五单双”的“银行家舍入法”
End Sub
Sub 行列号定位()
Range("a1:c4")(3, 2).Select '利用行号与列号定位
Range("a1:c4")(1.5, 2.5).Select '行列号也可以使用小数
End Sub
小结:
1.这个索引值是参照前一个单元格区域左上单元格进行定位引用的
2.索引值可以是正数,负数,零值,小数
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.索引值可以是正数,负数,零值,小数
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(行号,列号)
Sub cells基本写法()
Cells(3, 4).Select '行列号均为数字
Cells(2, "c").Select '行为数字,列为列标字母
Cells.Select '全选
End Sub
cells可以像range一样可以参照前面的单元格位置
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中
Sub 嵌套()
Range(Range("b1"), Range("f11")).Select '这种嵌套方法写变量比较麻烦
Range(Cells(3, 2), Cells(11, 6)).Select '这种嵌套方法写变量比较方便
End Sub
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单元格区域的表示方法,还有一种简单的写法
写法: [单元格地址] '注意:中括号中的单元格地址并不需要双引号("")
Sub 单元格简写()
[a3].Select ' 单元格引用
[b2:c6].Select '单元格区域引用
[a3,b2:c6,b8:d12].Select '多区域引用
[a:a].Select '整列引用
[1:1].Select '整行引用
End Sub
单元格简写的也支持引用子集
Sub 子集引用()
[b2:c6].Item(3).Select
Range("b2:c6")(3).Select
[b2:c6].Cells(4).Select
End Sub
Sub 动态区域的引用()
a = Application.CountA([a:a])
b = Application.CountA([1:1])
Range(Range("a1"), Range(Chr(64 + b) & a)).Select '利用chr函数,让字母形式的列号也支持变量
End Sub
Sub chr函数字符循环()
For i = 1 To 65535
Cells(i, 1) = i
Cells(i, 2) = Chr(i)
Next
End Sub
3-8.三种单元格引用小结
Sub range引用区域且有变量()
Dim i
i = 1
Range("a1:c" & i).Select '引用单元格是区域且有变量
Cells(i, "c").Select '引用的是单个单元格且有变量
[a1:19].Select '引用的是区域或单元格且无变量
End Sub
3-9. 行、列引用
行列引用
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 属性
返回指定区域中第一块中的第一列的列号
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.单元格的值与地址
单元格的值表示方法
Sub 单元格值表示()
a = [a1].Value '实际是什么,就是什么
b = [a1].Text '看到是什么,就是什么
c = [a1]
End Sub
注意:一个单元格可以省略value,多单元格区域不能省略
Sub 多区域赋值()
Range("e1:e4") = Range("d1:d4").Value
End Sub
单元格地址与引用
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代表不固定,默认是绝对引用
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 方法
将单元格区域复制到指定的区域
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.被赋值的区域格式全部去掉
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.插入与删除
工作表中单元格,行与列的插入与删除
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,工作表中活动单元格只有一个
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已使用区域
Option Explicit
Sub 宏3()
Range("C4").Select
Selection.CurrentRegion.Select
End Sub
Sub 宏4()
Range("G6").Select
End Sub
3-16.currentregion当前区域
Range.CurrentRegion 属性
返回一个 Range 对象,该对象表示当前区域。
Sub 当前区域()
[a1].CurrentRegion.Select
[f8].CurrentRegion.Select
End Sub
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
如果表中只有一个区域,两者最后的结果是一样的
只是表达方式不一样
Sub u与c()
Sheet3.UsedRange.Select
[a1].CurrentRegion.Select
End Sub
3-17.单元格的offset
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 对象的表达式。
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 对象的变量。
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 对象的变量。
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