运行过滤器后自动运行 VBA 代码 [英] Run VBA code automatically after running a filter

问题描述

我编写了一个代码，用于对员工及其资格进行分类.为了剔除具有不需要的资格的员工，我对每一列都应用了一个过滤器，这些列标题为他们的资格类别.

I've got a code written that categorizes employees along with their qualifications. In order to weed out employees with unwanted qualifications I have applied a filter to each column that titles the category of their qualification.

我编写了我的 VBA 代码，以便使重复的名称和资格不可见，以便于定位.但是，我无法让代码自动运行.

I've written my VBA code in order that repetitious names and qualifications are made invisible for ease of location. However, I am unable to get the code to run automatically.

目前我可以让代码运行的唯一方法是将其设置为

Currently the only way I can get the code to run is by setting it to

Private Sub Worksheet_Change(ByVal Target As Range)，然后更改任意单元格的值.

Private Sub Worksheet_Change(ByVal Target As Range) and then changing the value of an arbitrary cell.

我在以下位置找到了我认为正确的解决方案:

i found what I believe to be the correct solution at:

http://www.ozgrid.com/forum/showthread.php?t=72860

但我无法理解.

有没有一种方法可以运行此代码而无需在过滤器运行后选择和取消选择单元格?

Is there a way to run this code without having to select and deselect a cell after the filter has run?

推荐答案

我文章中的要点 使用 VBA 捕获对过滤列表的更改

文章中有更多细节和示例文件，要点总结如下

There is more detail and a sample file with the article, the key points are summarised below

1. 在 A1 中添加了一个虚拟"工作表，其中一个 SUBTOTAL 公式指向回在主工作表上过滤的范围.
2. 一个 Worksheet_Calculate() 事件被添加到虚拟"工作表中，当过滤器更改时 SUBTOTAL 公式更新时会触发此事件.

1. A "dummy" WorkSheet is added with a single SUBTOTAL formula in A1 pointing back to the range being filtered on the main sheet.
2. A Worksheet_Calculate() Event is added to the "dummy" WorkSheet, this Event fires when the SUBTOTAL formula updates when the filter is changed.

如果希望将工作簿计算作为Manual

The next two setps are needed if it is desired to run the Workbook Calculation as Manual

1. 添加一个 Workbook_Open 事件，将除Dummy"以外的所有工作表的 EnableCalculation 属性设置为 False.
2. 在计算模式下运行工作簿

1. Add a Workbook_Open Event to set the EnableCalculation property of all sheets other than "Dummy" to False.
2. Run the Workbook in Calculation mode

这篇关于运行过滤器后自动运行 VBA 代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！