Find Autofilter Header range

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
 
J

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
 
D

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
 
P

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
 
D

David

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
 
P

Patrick Molloy

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top