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

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