It was like riding a lazy snail through molasses in January -- but slower! Usually an Excel Advanced Filter is a speedy way to extract data from a table, but things weren't working right in a sample file that I got last week.
就像在一月份在糖蜜中骑着懒惰的蜗牛一样-但速度要慢一些！ 通常，Excel 高级筛选器是一种从表中提取数据的快速方法，但是在上周获得的示例文件中，一切都无法正常进行。
And despite what my high school English teachers might think, you can't mix too many similes, when trying to describe excruciating slowness.
The sample file had code that ran an Advanced Filter in Excel 2007. The code ran quickly in Excel 2003, but screeched to a near halt in Excel 2007. What was the problem?
该示例文件中的代码在Excel 2007中运行了高级筛选器。该代码在Excel 2003中快速运行，但是在Excel 2007中几乎停顿了。问题是什么？
病征 (The Symptoms)
When the code ran in Excel 2007, it looked like the extracted rows were being pasted in the second worksheet, one row at a time. Aha! Turn off the screen updating -- a simple solution. You'd think.
当代码在Excel 2007中运行时，看起来提取的行被粘贴到第二个工作表中，一次粘贴一行。 啊哈！ 关闭屏幕更新-一个简单的解决方案。 你会想。
Even with the screen updating turned off, the code barely crawled along. It took almost 3 minutes to extract 1500 rows -- maybe a millisecond faster than it ran with screen updating turned on. Who has that kind of time?
即使关闭了屏幕更新，该代码也几乎没有爬行。 提取1500行花了将近3分钟的时间-可能比打开屏幕更新的速度快一毫秒。 谁有这样的时间？
继续猜 (Guess Again)
In the next round of solution guessing, I got rid of the few formulas in the worksheet and criteria range. There wasn't anything too complex, but maybe that was slowing things down.
I also changed calculation to manual at the start of the code, then set it to automatic at the end of the code.
Neither of those changes had any effect on the code's speed.
清除数据 (Strip the Data Clean)
In round 12 of testing (I've lost track of the test count), I copied the data, and pasted it as values into a new workbook. The code ran like lightning. In July. With jet engines. Hmmm.
在第12轮测试中(我失去了对测试计数的了解)，我复制了数据，并将其作为值粘贴到新的工作簿中。 代码像闪电一样运行。 在七月。 使用喷气发动机。 嗯
Maybe it was the formatting and styles in the original file that were slowing things down. To test that theory, I formatted the original table with Normal style, which removed all the borders and fill colour.
That didn't improve things, but when I removed the red fill from the heading cells, I noticed a red comment marker in one of the cells.
鞭打成形状 (Whip Things Into Shape)
Could a comment be the problem? It didn't seem likely, but as soon as I deleted the comment, the code ran perfectly. Put the comment back, and it slowed to a crawl again.
可能是评论吗？ 似乎不太可能，但是当我删除注释后，代码就完美地运行了。 将评论放回去，它又变慢了爬行的速度。
好奇与好奇 (Curiouser and Curiouser)
When I tried to create a sample file to demonstrate this problem, things got even stranger. I created a table with a comment in the heading, and ran the code, expecting it to be slow. It ran quickly, in several tests.
当我尝试创建示例文件来演示此问题时，事情变得更加陌生。 我创建了一个在标题中带有注释的表，并运行了代码，期望它很慢。 在多个测试中，它运行很快。
Then I added a shape to the worksheet, and assigned a macro, to make it easier to run the code. The code slowed down again.
I deleted the shape, and the code was still slow, and I had to delete the comment to speed it up again.
判决 (The Verdict)
If your Advanced Filters are running slowly in Excel 2007, try removing any comments in the table heading cells. You could delete them at the start of a VBA procedure, run the filter, then add the comments at the end of the code.
如果您的“高级筛选器”在Excel 2007中运行缓慢，请尝试删除表标题单元格中的所有注释。 您可以在VBA过程开始时将其删除，运行过滤器，然后在代码末尾添加注释。
The problem seems to occur if there are heading comments, and a shape is added later, as you can see in the short video demonstration below.
Fortunately, this problem appears to be fixed in Excel 2010, so if you upgrade, you should be able to have comments and shapes, without slowing down the Advanced Filters.
Update: In the comments, PDLobster suggests the following solution, to speed up the filters -- thanks!
- Turn off all filters 关闭所有过滤器
- Select cell A1 选择单元格A1
- Turn Wrap Text ON 开启自动换行
- Select the entire worksheet 选择整个工作表
- Turn Wrap Text OFF 关闭自动换行
观看视频 (Watch the Video)
To see the steps for reproducing and solving the Advanced Filter speed problem, you can watch this short Excel video.