Run VBA code automatically after running a filter

Question

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.

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) 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

But I cannot make sense of it.

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

Answer 1

The key points from my article Trapping a change to a filtered list with VBA

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

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.

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

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