Find Autofilter Header range

[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

 

[Jacob Skaria]

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

 

[David]

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

 

[Patrick Molloy]

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

 

[David]

Patrick
You are a star
Thanks

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

actually, look at your own reply to Jacob . .. I pretty much stole your own
code