Find Autofilter Header range

  • Thread starter Thread starter David
  • Start date Start date
D

David

I have code from this community to 'reset' the autofilter for each worksheet
where AutoFilterMode = true (Thanks Patrick Molloy)
I see that I need to specify the target range for the filter headers
In my workbook filter headers may be on row 1, row 2 , etc
Is there a way to determine for each worksheet where the filter headers are
located?
Thanks
 
By reset if you mean remove autofilter or show all data

Dim sh as Worksheet
For each Sh in ActiveWorkbook.Worksheets
'to remove autofilter
'Sh.AutoFilterMode = False
Sh.ShowAllData
Next

If this post helps click Yes
 
Jacob
Thanks for your response
By 'reset' I meant remove and re-apply the autofilter on sheets where it
exists
like this:

I ned to identify the Autofiltercolumn header range in the code below as in
the title of th op

Dim wks as worksheet
For each wks in ThisWorkbook.Worksheets
If wks.AutofilterMode Then
Dim target as range
Set target = range("MyAutofilterHeaderRange")
'turn filter off
target.autofilter
'turn filter back on again
target.autofilter
End if
Next wks
 
Option Explicit
Sub ResetFilters()
Dim target As Range
Dim ws As Worksheet
For Each ws In Worksheets
If ws.AutoFilterMode = True Then
Set target = ws.AutoFilter.Range
target.AutoFilter
target.AutoFilter
End If
Next
End Sub
 
Patrick
You are a star
Thanks

Patrick Molloy said:
Option Explicit
Sub ResetFilters()
Dim target As Range
Dim ws As Worksheet
For Each ws In Worksheets
If ws.AutoFilterMode = True Then
Set target = ws.AutoFilter.Range
target.AutoFilter
target.AutoFilter
End If
Next
End Sub
Click to expand...
 
actually, look at your own reply to Jacob . .. I pretty much stole your own
code ;)
 
Back
Top