Execute macro whenever data filter selection made

jdaywalt

Board Regular
Joined
Jul 18, 2012
Messages
63
I have a file containing 20 columns with an Auto Filter applied across the headers. Below the headers, there are 4 rows (10:13) that I want to always be hidden, regardless of what filter may be selected in any of the columns. I have an Auto-Open macro that hides these rows whenever the file is opened, but when users select various filters, then ultimately go back to "Select All", rows 10:13 become unhidden. I want to force the "hide rows" macro to execute every time someone selects a filter to ensure these rows always stay hidden.

I thought I was being creative by building a SUBTOTAL (count) formula that did a count of the rows, thinking that when the user selects a filter, the result of the "count" formula would change, thereby triggering the Change event, and executing the macro. It did not work, I assume because the physical formula itself did not change??

Anyway, looking for (hopefully) a simple approach to accomplish my objective. Any ideas?

Here is my current code (cell A1 contains the formula =SUBTOTAL(3,A12:A1000):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Range("A12:A16").EntireRow.Hidden = True
End If
End Sub
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,093
Members
414,501
Latest member
mdhaumyu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top