一帖全了解：VBA编程中的单元格对象！

每天一篇Excel技术图文

微信公众号：Excel星球

NO.134-什么是单元格对象

作者：看见星光

 微博：EXCELers / 公众号：Excel星球

HI，大家好，我是星光。在前面的章节咱们先后学习了VBA编程的各种概念、数据类型和常用语句；从这章开始咱们来学习几类常用对象：工作簿、工作表和单元格。

在什么是对象那章咱们讲过，对象具有层次性，层次可以理解为上下级的隶属关系。对于VBA来说，Excel程序是第1层对象、工作簿是第2层对象、工作表是第3层对象，到了单元格，那就已经是红四代，属于第4层对象了。如果我们完整的描述一个单元格对象，示例语句如下▼

Application.Workbooks(1).Worksheets(1).Range('a1')

翻译过来就是Excel它太爷爷的爷爷的儿子的孙子，是个单元格的鬼东西。

还记得吗？曾经有个前辈谆谆教导我们说，有为青年要扎根社区，深入基层，只争朝夕，不负韶华——所以，关于VBA常用对象那些事儿，咱们还是从网格员单元格学起吧！

1丨

单元格的表达方式

单元格是Excel中最基础也是最重要的对象，毕竟绝大部分数据都是储存在它家的。以工作表左上角的第一个单元格为原点，向下为行，向右为列，构成了一个由行、列组成的坐标体系。就好比我们学生时代接触过的x轴y轴坐标体，因此每一个单元格都有它的地址或者说坐标。

这就延伸出了多种表示单元格的方法。以A1单元格为例，以下方式都是可以的。

• 
  

• 
  

• 
  

[A1]Range('A1')Cells(1,1)或者Cells(1,'A')

三种方式都支持字母大小写，但各有优缺点。

第1种方式书写最简便，但不支持使用变量。另外这是一种比较古老的方式，属于被兼容的状态，稳定性和效率都有瑕疵。就好比2003版Excel虽然现在也能用，但很多新功能它都没有——我的意思是你最好养成不使用[A1]的好习惯。

第2种方式貌似书写比较麻烦，不过它支持使用变量，更重要的是三种方式里只有它自带列出单元格对象的方法和属性。所以虽然它书写麻烦，却是前人栽树后人乘凉的典范。

第3种方式两个参数都支持使用数字变量，最为灵活，运算效率也最高。举个例子，代码如下。

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

Sub text()    Dim i As Long, x As Long, y As Long    i = 1    Range('a' & i) = '看见星光' 'A1单元格    x = 2: y = 2    Cells(x, y) = '看见星光' 'B2单元格End Sub

第3行代码将变量i赋值为1，第4行代码在A1单元格输入字符串'看见星光'。

第5行代码将变量x赋值为2，变量y也赋值为2。第6行代码在当前工作表第2行第2列交叉的单元格，也就是B2单元格输入字符串'看见星光'。

2丨

特殊单元格的表达方式

第1种：当前活动单元格

当前活动单元格，可以使用ActiveCell语句来表示。

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

Sub test2() If ActiveCell.Address = '$A$1' Then '判断当前活动单元格是否为A1 MsgBox '你需要查询的内容是：' & Cells(1, 1).Value Else MsgBox '请选择A1单元格再运行代码' End IfEnd Sub

第2行代码获取当前活动单元格的地址，并判断是否为A1单元格。需要注意的是，在VBA中等号判断区分字母大小写，这里的A1必须大写，不能使用小写a1。

……

第2种：偏移单元格

单元格的Offset属性，可以以指定单元格为起点偏移到另一个单元格，结果返回一个单元格对象。

语法格式如下▼

• 
  

Range.Offset(行,[列])

例如，已知变量k，并假定k=10。以A1单元格为起点，选中向下移动k行后的指定单元格，代码如下▼

• 
  

• 
  

• 
  

• 
  

• 
  

Sub test13() Dim k As Long    k = 10 Range('A1').Offset(k, 0).SelectEnd Sub

需要注意的是，该语句选取的是A11单元格，不是A10。Offset偏移指定的行数不包含起点自身。另外，Offset的参数可以是负数，实现向上或向左偏移。它的第二参数还可以省略，省略意味着偏移列数为0，以上过程第4行代码也可以简写成：

• 
  

Range('A1').Offset(k).Select

……

第3种单元格的End属性。

单元格的End属性，返回一个Range对象，效果等同于快捷键<Ctrl+方向键>。语法格式如下▼

• 
  

Range.End(方向)

方向包含4种情况。向上xlUp，向下xlDown，向左xlToLeft，向右xlToRight。

以下代码可以返回当前工作表A列最后存在数据的单元格。

• 
  

• 
  

• 
  

Sub test14()    Cells(Rows.Count, 1).End(xlUp).SelectEnd Sub

Cells(Rows.Count,1)语句，返回当前工作表A列最后一个单元格，使用End(xlup)语句，等同于按下<Ctrl+↑>键，返回A列最后一个储存数据的单元格。

……

第4种单元格的SpecialCells方法。

它可以返回一个与指定类型或值相对应的单元格对象。

简单说，它类似于基础操作中的定位功能（快捷键F5或Ctrl+G)。可以快速定位常量、错误值、空值等特殊单元格。

比如定位当前工作表的错误值单元格：

• 
  

Cells.SpecialCells(xlCellTypeFormulas, 16).Select

再比如快速删除A列单元格为空白的行：

• 
  

Columns('A:A').SpecialCells(xlCellTypeBlanks).EntireRow.Delete

相关语句均可以通过录制宏获取，初学阶段并不需要刻意去记忆，用到时录制个宏就可以了——能躺赢谁也不愿意走两步不是？

3丨

单元格区域的表达方式

重点说一下单元格区域的表达方式，这分为了很多种常见的情况。

第1种，全部单元格

举个常用的例子，清空当前工作表全部单元格的内容。以下代码中的Cells即指全部单元格，ClearContents是清空单元格内容的意思。

• 
  

• 
  

• 
  

Sub test3() ActiveSheet.Cells.ClearContentsEnd Sub

……

第2种，指定单元格区域

比如表达A1:A10单元格区域，可以使用以下几种方式。

• 
  

• 
  

• 
  

• 
  

[A1:A10]Range('a1:b10') Range('a1','b10') Range('a1').Resize(10,2)

第1种和第2种方式，各自类似于[A1]和Range('A1')，优缺点咱们都讲过了，这里不再重复。第3种方式比较少用。

第4种方式数组读写那一章咱们详细讲过了。它支持变量使用，最常用也最灵活，可以以单元格或区域的首个单元格为起点，同时向右向下扩展对应的行数和列数，扩展的行数和列数包含起点自身，最后返回一个单元格对象。

• 
  

Range('a1').Resize(10,2)

以A1单元格为起点，向下扩展10行，向右扩展2列，返回A1:B10单元格区域。

……

第3种，数据相连区域

在我们过往作业的VBA解法里，你会经常见到以下语句。

• 
  

Range('a1').CurrentRegion

代码中使用了单元格对象的CurrentRegion属性，它的作用是返回与指定单元格相连的数据区域，直到边缘是任意空行和空列。它的结果是一个Range对象。

由于绝大部分工作表的数据都是从A1开始编辑的，因此上述代码通常也就表达了工作表的数据区域。

当然，这并不绝对。比如下图所示的情况。

• 
  

• 
  

• 
  

Sub test7() Range('a1').CurrentRegion.SelectEnd Sub

运行以上代码，它只选取了A1:D7单元格区域，遗漏了A9:D9；毕竟A8:D8是空白行，以至于后者和A1数据区域并不相连。

这个时候可以使用工作表的UsedRange属性。

……

第4种，已使用单元格区域

UsedRange并不是单元格的人，她是属于工作表的属性，可以返回指定工作表已使用的单元格区域，结果也是一个单元格对象。

• 
  

• 
  

• 
  

Sub test8()    ActiveSheet.UsedRange.SelectEnd Sub

运行以上语句可以选取如下图所示的结果。注意它选取的是已使用的单元格区域，所谓已使用，并非指单元格中储存了数据，而是指有被编辑过的痕迹，比如D13单元格。

此外需要说明的是，即便从未编辑使用过的新建工作表，依然也存在UsedRange，也就是默认的A1单元格。因此使用UsedRange并无法直接判断一张工作表是否空白。

以下语句可以清除当前工作表除了第一行以外的内容，常用于保存标题，清除数据明细记录。

• 
  

ActiveSheet.UsedRange.Offset(1).ClearContents

……

第5种，精确选取数据区域

如下图所示的数据列表，如果我们需要动态选取存在数据的区域，比如A1:D9，很明显既不能使用CurrentRegion语句，也不能使用UsedRange语句。

而运行以下代码，即可以精确选取存在数据的A1:D9单元格区域。

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

Sub test10()    Dim rng As Range, l As Long    l = Cells(Rows.Count, 1).End(xlUp).Row    Set rng = Range('a1:d' & l)    rng.SelectEnd Sub

第3行代码返回A列最后一个储存数据的单元格的行号，并赋值变量l。

第4行代码将指定单元格区域赋值变量rng。第5行代码选定该区域。

相比于之前咱们介绍的CurrentRegion语句和UsedRange语句，以上代码既不会盲目臃肿的选取多余的范围，比如A10:D13，也不会遗漏间隔空行空列，比如A9:D9。当然，它也并非十全十美。它需要依赖指定列为坐标，比如本例中的A列姓名列，如果A列最后一行姓名为空，而成绩非空，这种方法也会造成数据遗漏。

如何解决这个问题？我们下一章再聊。

……

第6种，交叉重叠区域

Intersect是Application对象的方法，可以返回两个或多个单元格区域交叉重叠的部分。

比如选取当前工作表c:d列已使用的区域，也就是C:D列和当前表已使用区域交叉的部分。语句如下▼

• 
  

• 
  

• 
  

Sub test9() Intersect([c:d], ActiveSheet.UsedRange).SelectEnd Sub

……

第7种，不连续单元格区域

比如选取A1:A10,C1:C10两个单元格区域。常用的方法有以下两种。

• 
  

• 
  

• 
  

• 
  

Sub test4()    Range('a1:a10, c1:c10') = 1    Union(Range('a1:a10'), Range('c1:c10')) = 2End Sub

两种方式各有特点。

第1种方式它的参数是以半角逗号分隔的单元格地址，长度不能超过255个字符，每一个逗号指定的单元格区域都是独立的个体。

第2种方式是Union语句，它的参数是单元格对象，一个Union语句最多只能有30个参数，也就是30个单元格对象。它的结果也是返回一个整体的单元格对象，因此我们可以通过嵌套Union语句打破数量30的限制。比如:

• 
  

Union(Range('a1:a3'),Union(Range('b1:b3'),Range('f1:f3'),Range('e1:e3'))

上述代码合并了4个单元格区域，但第2个Union语句将3个单元格区域合并为了一个单元格对象，因此对于外围的Union语句来说，它只使用了2个参数，也就是2个单元格对象。

两个语句都支持使用变量，相比Range语句，Union语句更加灵活。不过Range也有自己的独到之处：它是联邦制，每一个逗号指定的单元格区域都是独立的个体。

什么意思呢？举个例子（特别说明，这个知识点了解就好，不用理解）。

• 
  

• 
  

• 
  

• 
  

• 
  

Sub test5()    Application.DisplayAlerts = False    Range('a2:a4,a5:a8').Merge    Application.DisplayAlerts = TrueEnd Sub

代码中'a2:a4,a5:a7'，是单元格相连的区域，貌似应等同于a2:a7；但事实上并不是，它们是独立的个体，因此运行以上代码，个体区域单独合并单元格，返回结果如下所示，A2:A4合并单元格，A5:A8再合并单元格。

……

• 
  

• 
  

• 
  

• 
  

• 
  

Sub test6() Application.DisplayAlerts = False Union(Range('a2:a4'), Range('a5:a8')).Merge Application.DisplayAlerts = TrueEnd Sub

上述代码使用了Union语句，它会将a2:a4和a5:a8强力合并成为一个单元格对象，内部区域不再是独立的个体。因此它返回的结果如下图所示，A2:A8成为了一个合并单元格。

第8种，跨工作表区域

这是VBA新手最容易犯错的知识点，99%的人都犯过同样的错误，认真脸。

比如将工作表名称为'看见星光'的A1:DX区域的数据存入数组arr，也许你会写出以下代码：

• 
  

• 
  

• 
  

Sub test7()    arr = Worksheets('看见星光').Range('a1:d' & Cells(Rows.Count, 1).End(xlUp).Row)End Sub

摊手，以上代码是错误的，以下代码是正确的。

• 
  

• 
  

• 
  

Sub test7() arr = Worksheets('看见星光').Range('a1:d' & Worksheets('看见星光').Cells(Rows.Count, 1).End(xlUp).Row)End Sub

两段代码有何不同？

在什么是对象一章里咱们讲过，对象的层次具有默认性。如果单元格对象前未指定父对象，则默认为当前工作簿当前工作表的单元格对象。第一段代码，尽管Range('a1:d'……)前面指定了工作表对象，但Cells(Rows.Count)前面并没有指定工作表对象，则默认为当前工作表对象的Cells，而非工作表'看见星光'的Cell……这就不对了不是？

以上代码过长，在实际应用中，常借助With语句简化，示例代码如下所示。

• 
  

• 
  

• 
  

• 
  

• 
  

Sub test7()    With Worksheets('看见星光')        arr = .Range('a1:d' & .Cells(Rows.Count, 1).End(xlUp).Row)    End WithEnd Sub

4丨

行和列的表达方式

最后说一下行和列的选取方式，比较简单。

先说行。

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

Sub test11() Rows(1).Select '第1行 Range('a1').EntireRow.Select '第1行 Rows('1:2').Select '第1:2行 Range('a1:a2').EntireRow.Select '第1:2行 Range('a1,a3,a5').EntireRow.Select '第1/3/5行End Sub

第2行和第3行代码均表达了第一行区域。第4行和第5行代码均表达了第1和第2行区域。第6行代码表达了第1、3、5不连续的行区域。

再说列。列和行的表达方式十分相似。

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

Sub test12()    Columns(1).Select '第1列    Range('a1').EntireColumn.Select '第1列    Columns('a:b').Select 'a:b列    Range('a1:b1').EntireColumn.Select 'a:b列    Range('a1,c1,f1').EntireColumn.Select 'a、c、f3列End Sub

……

没了，打完收功。

这一章我们集中了解了各种单元格的表达方式，下章开始我们会通过几个实战性的案例一起来了解一下单元格对象还有哪些常用的属性和方法，比如多表数据汇总、批量删除空行、单元格合并与反合并、数据快速查找和替换等等。


示例文件下载，百度网盘▼
https://pan.baidu.com/s/1yOtdVMyK0dTgB8jw7cfOFQ
提取码: bf2p

关注不会迷路