VBA操作单元格对象有哪些常用语句？

每天一篇Excel技术图文

微信公众号：Excel星球

NO.159-你有一个名叫单元格的对象

作者：看见星光

 微博：EXCELers / 知识星球：Excel

HI，大家好，我是星光。在「零基础学VBA编程」前面的章节里，先后给大家介绍了如何使用VBA代码实现单元格遍历和删除、查找与替换、排序与合并等；本章再给大家总结一下其它常用的套路性代码，涵盖了选中、清除、复制粘贴、设置格式、去重复、筛选等。内容较多，篇幅较长，目测非常有利于催眠，所以建议睡个回笼觉，先马后看。

1丨

选中或激活

以下两个语句都可以选中当前工作表的A10单元格。

• 
  

• 
  

Range('a10').SelectRange('a10').Activate

两条代码的作用大部分情况下是相同的，所不同的是Activate可以从被选取的单元格区域中激活指定单元格。

测试代码如下▼

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

Sub SelectActivate()    Range('a1:c10').Select    '此时默认活动单元格为矩形右上角第一个单元格    '也就是A1    Range('a5').Activate    '在被选取的A1:A10区域中激活A5单元格End Sub

代码运行后，系统会选取A1:C10单元格区域，并激活A5单元格。

需要注意的是，不管Select还是Activate都只能在当前工作表中选取指定单元格或区域，如果指定的单元格不是当前工作表，会返回错误提示。

错误代码如下▼

• 
  

• 
  

• 
  

Sub ShtRng() Worksheets('测试').Range('a1').SelectEnd Sub

正确代码如下▼

• 
  

• 
  

• 
  

• 
  

• 
  

Sub ShtRngR()    Worksheets('测试').Select    '先选取激活工作表，再选取工作表内的单元格    Range('a1').SelectEnd Sub

2丨

清除单元格中的信息

一个单元格的属性，不但有内容，还有格式、超链接、批注等——所以清除单元格中的信息也就分为了多种情况，如下图所示。

相关代码都可以通过录制宏获取，不过最常用的有两条没有鱼，还是需要花费7秒时间记忆一下。

一条是全部清除单元格的各种信息，包括单元格格式、边框线等。

• 
  

• 
  

• 
  

Sub CellsClear() Cells.ClearEnd Sub

另外一条是只清除单元格的内容，保留格式等其他信息，常用于清空模版数据，以待放置新数据。

• 
  

• 
  

• 
  

Sub CellsClearContents()    Cells.ClearContentsEnd Sub

3丨

复制粘贴

如果我们需要将A1:D5单元格区域的数据复制到H1:K5区域，可以使用以下代码▼

• 
  

• 
  

• 
  

Sub CopyRng1() Range('a1:d5').Copy Range('h1:k5')End Sub

粘贴区域可以只指定左上角的首个单元格，系统会根据复制区域的大小，自动扩展目标区域，因此以下代码更为常用。

注意粘贴单元格的变化..▼

• 
  

• 
  

• 
  

Sub CopyRng2()    Range('a1:d5').Copy Range('h1')End Sub

Range对象的Copy方法完整语法格式如下：

• 
  

Range.Copy (Destination)

参数Destination指定了粘贴单元格的目标区域，可以省略，如果省略Excel会将单元格对象复制到剪贴板中。

使用Range对象的PasteSpecial方法可以将剪贴板的Range粘贴到指定区域，并可以选择性的粘贴对象的部分属性——这对应了Excel基础操作中的选择性粘贴功能。

我举个例子。

• 
  

• 
  

• 
  

• 
  

• 
  

Sub CopyRng3()    Range('a1:d5').Copy    Range('h1').PasteSpecial xlPasteAll    Application.CutCopyMode = FalseEnd Sub

第2行代码将A1:D5区域复制到剪贴板。第3行代码将剪贴板内单元格对象的全部内容（xlPasteAll）复制粘贴到以H1单元格为左上角的区域。

这种复制粘贴的方式，源单元格区域会出现虚线边框，因此第4行代码取消剪切或复制模式并清除移动边框。

……

选择性粘贴对话框▼

说一下PasteSpecial的完整语法格式▼

• 
  

Range.PasteSpecial(Paste,Operation,SkipBlanks,Transpose)

Range是粘贴的目标单元格或区域。

Paste指定了粘贴的源单元格区域的属性，常用的有格式、列宽、公式、值等。对应上图的区域1。

Operation表示粘贴的运算，有加减乘除无等，默认为无，对应上图的区域2。

SkipBlanks表示是否跳过空格，默认为False，对应上图的区域3。

Transpose表示是否进行转置，默认为False，对应上图的区域4。

……

选择性粘贴的相关属性和代码均可通过录制宏获取，通常并不需要强行记忆。打个响指，吃一包好多鱼，有几个常用的套路性语句大家还是需要深入了解一下。

也许你知道，也许你不知道，但很快你就知道的是，直接使用复制粘贴，不会将源单元格的列宽复制到目标区域——但借助选择性粘贴列宽，可以解决这个问题。

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

Sub CopyRng4()    Range('a1:d5').Copy    Range('h1').PasteSpecial xlPasteColumnWidths    Range('h1').PasteSpecial xlPasteAll    Application.CutCopyMode = FalseEnd Sub

第2行代码将源单元格复制到剪贴板，第3行代码选择性粘贴列宽，第4行代码粘贴全部属性。

……

有朋友可能会想，选择性粘贴还有一个比较常用的功能，将公式的计算结果粘贴为数值——但这在VBA编程上并不常用。将公式转换为数值，更常使用的方式是直接赋值▼

• 
  

• 
  

• 
  

• 
  

• 
  

Sub CopyValue() Dim rng As Range Set rng = Range('a1').CurrentRegion rng.Value = rng.ValueEnd Sub

第4行代码将单元格的值属性，即去除了公式等其他属性，写入单元格区域中，这和复制后选择性粘贴数值相比……刀郎是这么说的，它来得那么快来得那么直接，就算我心狂野，也无法将火熄灭……

……

4丨

设置单元格格式

当我们需要将文本型数值写入一个全新的工作表时，由于目标区域默认的单元格格式为常规，会导致文本型数值出错，比如身份证后3位变为0等。

解决这个问题最简单的方法是设置指定区域的单元格格式。

示例代码如下▼

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

• 
  

Sub rngFormat()    Dim arr    arr = Worksheets('数据表').Range('a1').CurrentRegion    Worksheets('结果表').Select    Range('d:d').NumberFormatLocal = '@' '设置文本格式    Range('a1').Resize(UBound(arr), UBound(arr, 2)) = arrEnd Sub

代码解析：

第3行代码将数据源存入数组arr。第4行代码选中名为'结果表'的工作表。第5行代码设置D列单元格格式为文本，避免证件号数据变形。第6行代码将数组arr的数据写入指定区域。

第5行设置单元格格式的代码也可以使用▼

• 
  

Range('d:d').NumberFormat = '@'

NumberFormatLocal是通过本地用户语言来定义单元格格式，NumberFormat是使用通用格式代码来定义单元格格式。这在中英文系统下可能会有差异，比如中文系统常规格式叫做G/通用格式，但这在英文系统下就不能用了，更推荐使用通用格式General——这点了解一下即可，防止将来找个外国异性朋友叽里呱啦什么嗯？

不同数据类型的格式代码可以通过录制宏获取，这里不再展开描述。

5丨

删除重复项

根据指定列删除数据区域内的重复值，可以使用单元格对象的RemoveDuplicates方法。

举个例子，删除C列重复的人名，获取唯一值列表，代码如下▼

• 
  

• 
  

• 
  

Sub DistinctRngData()    Range('c:c').RemoveDuplicates 1, xlYesEnd Sub

RemoveDuplicates方法语法格式如下：

• 
  

Range.RemoveDuplicates(Columns,Header)

有两个参数，第1个参数表示以区域中第几列作为删除重复项的依据列，第2个参数表示区域是否包含标题行，默认不包含。

第1参数支持使用数组指定多列数据。比如……删除A:C列区域中，B:C两列记录同时重复的数据，代码如下▼

• 
  

• 
  

• 
  

Sub DistinctRngData2()    Range('a:c').RemoveDuplicates Array(2, 3), xlYesEnd Sub

6丨

筛选

最后聊一下筛选……算了，还是不聊了，无赖脸，关于筛选咱们放到单元格事件里再说吧。

打个响指，关于单元格对象的常用操作到此也就算告一段落了。说几句题外话，大部分Excel自带的功能，比如分列、筛选、查找替换等，都可以通过数组循环的方式实现，而且效率往往也更高，所以大家初期学习的重点还是数组四篇，那是一剑破万法的存在；至于单元格对象等，可以先了解，用到时教程里直接抄代码都是可以的。

总结一句话就是：不要老是搞对象。

挥挥手下期再见。

更多零基础学VBA编程教程▼

菜单->教程->零基础学VBA

练手下载百度网盘..▼
https://pan.baidu.com/s/1kUJEBlZ_z6demPh-onNcEQ

提取码: 6rdx

练习..▼
1.清空当前工作表所有单元格的信息
2.将'数据表副本'的数据复制到'数据表'，并保持列宽和数据格式不变
3.将数据表A列的公式计算结果转数值
4.获取数据表C列不重复人名的名单，并放置到结果表A列