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