【笔记】Excel 2021｜重要篇｜（一）VBA精准控制选择范围、VBA提高运行速度并降低运行时的内存消耗

  最近总是遇到只有Excel表、没有数据库的情况，不太可能让别人搭Python环境来建数据库，但对常用数据库的使用也不太熟悉，也没买服务器不能让别人远程操控。再加上Python操作Excel的效率令人发指，不妨退而求其次直接学会VBA。

  没想到坑挺多的，来分享一下其中的典型。考虑到之后可能还需要使用它，可能还会遇到其他天坑，因此，不妨将本文提到的作为VBA不可不说的注意事项（第一篇）。

文章目录

• 1 要想运行之后Excel文件的大小不爆炸，你需要……
• • 慎用Cells.xxx、Range(A:A)等选择范围极其广的，精确选择范围
• 2 要想运行时内存不爆炸，你需要……
• • 禁用应用程序屏幕更新、禁用事件

1 要想运行之后Excel文件的大小不爆炸，你需要……

慎用Cells.xxx、Range(A:A)等选择范围极其广的，精确选择范围

（1）引言

举个例子，原始表格如下图所示，10KB：

我添加一个筛选器，设置成绩>19，结果如下，Excel文件大小不变：

接下来，我全选(也就是Cells的选择范围)，将这个筛选结果复制到另一个Sheet工作表中，并保存：

您猜怎么着？
——文件大小直接爆炸啦！

并且更糟糕的是，此时再删除新表，文件大小也不会完美复原了。

不过好消息是，Ctrl+Z撤回操作可以将大小完美复原。
但谁编程的时候会没事用Ctrl+Z啊？

（2）解决方式

最关键的是，确定范围的代码：

参考：

1. Excel VBA获取最后一行列_CSDN
2. Excel VBA-查找包含数据的最后一列_JavaRoad问答社区

对于行，确定最后一行的行号的方式是：

LastRow = Range("A" & Rows.Count).End(xlUp).Row
- LastRow = UsedRange.Rows.Count 
Range("A1:N" & LastRow).Select

第一行，令 LastRow = 第A列的行数；（如果不只是需要A列的行数，而是需要所有用过的行，就用UsedRange.Rows.Count）
第二行，精确选择所需要的范围，A1到N[LastRow]。

Rows.Count是当前版本最大的行号，2007年之前的版本最大行号是65535，而之后的版本增大了最大行数，变成1048576。这种方式获取的最后一行只会包括内容不为空的行，而UsedRange.Rows.Count找到的行，会包括仅设置了格式的行，因此建议用第一种。

对于列，确定最后一列的列号的方式是：

LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
Range(Cells(1,1), Cells(1, LastColumn)).Select

第一行，令 LastColumn = 第1行的列数；（如果不只是需要第一行的列数，而是需要所有用过的列，就用UsedRange.Columns.Count）
第二行，精确选择所需要的范围，第一行第一个，到第一行最后一个。

另一种方法：Cells.Find(What:="*", After:=ws.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)。我还没试过，来源于参考的第二篇内容。

倘若不确定最后一行/列的位置，直接Cells.Select，就容易出现选了非常非常多没用的东西的情况。

2 要想运行时内存不爆炸，你需要……

禁用应用程序屏幕更新、禁用事件

在每个编写的宏的最开始加上这三行：

ActiveWorkbook.Save
Application.ScreenUpdating = False
Application.EnableEvents = False

结尾加上这三行：

ActiveWorkbook.Save
Application.ScreenUpdating = True
Application.EnableEvents = True

这些代码禁用应用程序屏幕更新、禁用事件。这保证你的宏在运行时，屏幕不会闪来闪去的，Excel运存不会爆炸，同时较好地提高运行速度(亲测提高一倍左右)。等运行完了，屏幕才会统一发生变化。

参考：Excel，VBA：清除内存-码农家园-StackFlow译文，该文还提出使用Evaluate直接计算值，而不是将公式放入表中，会加速。