Remove Filters from all sheets

[magmike]

I have multiple sheets in my workbook and they are all often filtered
for certain results. However, when I want to seach the workbook as a
whole, it will only search through the filtered results - therefore I
am having to go to each sheet and clear the filters in order to search
the whole workbook.

Is there a way to do this en masse for all sheets and all columns?

Thanks in advance for your help!

magmike

 

[GS]

magmike brought next idea :

I have multiple sheets in my workbook and they are all often filtered
for certain results. However, when I want to seach the workbook as a
whole, it will only search through the filtered results - therefore I
am having to go to each sheet and clear the filters in order to search
the whole workbook.

Is there a way to do this en masse for all sheets and all columns?

Thanks in advance for your help!

magmike

One way...

Sub TurnOffFilterMode()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
If wks.FilterMode Then
With wks.UsedRange
.AutoFilter '//turn it off
.AutoFilter '//reset the dropdowns
End With
End If 'wks.FilterMode
Next 'wks
End Sub

 

[Don Guillett]

If you were using vba FIND to look for your value that would be true.
However, if you use application.match(yourvalue,yourcolumn) then you
can find in a filtered column.

MsgBox Application.Match("myvalue", Columns(2), 0)

 

[GS]

Don Guillett submitted this idea :

If you were using vba FIND to look for your value that would be true.
However, if you use application.match(yourvalue,yourcolumn) then you
can find in a filtered column.

MsgBox Application.Match("myvalue", Columns(2), 0)

Don,
While your reply doesn't address the OP's concerns for a way to remove
filters, I agree with you that it would be a better approach to what
he's trying to do.

Nice suggestion!

 

[magmike]

Don Guillett submitted this idea :



Don,
While your reply doesn't address the OP's concerns for a way to remove
filters, I agree with you that it would be a better approach to what
he's trying to do.

Nice suggestion!

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Garry,
Does this turn off AutoFilter or just clear the filters? Also, my
column headers are in Row 3 - does that change anything?
Also - OP?

Don,
Currently, I am just using the F3 find feature. Is your suggestion a
procedure that I would call using a button or macro? Sorry, but I am
not very well versed in vba - but love learning more about it. How
would I use the MsgBox Application.Match thing?

 

[Don Guillett]

Garry,
Does this turn off AutoFilter or just clear the filters? Also, my
column headers are in Row 3 - does that change anything?
Also - OP?

Don,
Currently, I am just using the F3 find feature. Is your suggestion a
procedure that I would call using a button or macro? Sorry, but I am
not very well versed in vba - but love learning more about it.  How
would I use the MsgBox Application.Match thing?

Send me the file dguillett1 @gmail.com

 

[GS]

magmike expressed precisely :

Garry,
Does this turn off AutoFilter or just clear the filters? Also, my
column headers are in Row 3 - does that change anything?

It turns off the filters (ie: no dropdowns), but turns them back on for
all columns starting at row1. That's why I put the 2nd statement on a
separate line, in case you had applied the filters to only a specific
range. In this case you could delete that 2nd line.

Also - OP?

"OP" is newsgroup speak for "Original Poster". In this case that's

 

[magmike]

magmike expressed precisely :







It turns off the filters (ie: no dropdowns), but turns them back on for
all columns starting at row1. That's why I put the 2nd statement on a
separate line, in case you had applied the filters to only a specific
range. In this case you could delete that 2nd line.


"OP" is newsgroup speak for "Original Poster". In this case that's
YOU!<g>




--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

Actually, I am just fine turning them all on - however, if I cared,
How could I format that line to specify which columns to turn on?

 

[GS]

magmike brought next idea :

Actually, I am just fine turning them all on - however, if I cared,
How could I format that line to specify which columns to turn on?

So then the code works as is for how you do it currently.

You can use AutoFilter on ranges. For example, if your sheet has 3 rows
for whatever before the actual data then you could use the data area
address...

Range("$A$3:$G$100").AutoFilter

...puts the dropdowns on your headers row. (assumes the range has data)

 

[magmike]

magmike brought next idea :


So then the code works as is for how you do it currently.

You can use AutoFilter on ranges. For example, if your sheet has 3 rows
for whatever before the actual data then you could use the data area
address...

  Range("$A$3:$G$100").AutoFilter

..puts the dropdowns on your headers row. (assumes the range has data)

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Yes it does work just fine as it is. But thank you for the additional
lesson. I am sure it will be handy eventually.

I assume that: Range("A:G").AutoFilter would also
work?

magmike

 

[GS]

magmike explained :

Yes it does work just fine as it is. But thank you for the additional
lesson. I am sure it will be handy eventually.

I assume that: Range("A:G").AutoFilter would also
work?

magmike

That will place dropdowns at the top of the columns rather than a
specified row.