"Autofilter: Show All" causing an error

[leimst]

Good Morning,
I recorded a macro which applies an Autofilter but in recording the macro, I
selected the "Show All" filter before setting any filters so that if a
filter was already being used I wouldn't being filtering data that was
already filtered on another criteria. But I found that if no filters were
selected at the time I ran my macro, then having the macro try to select
"Show All" when no filters were being used causes an error. Does anyone
know how to get around this?

Thanks in advance for any help!
Brian

 

[Richard Schollar]

Hi Brian

You could use On Error Resume Next to ignore this runtime error, but I would
just be tempted to use:

With Sheets("YourSheetName") 'amend as required
.AutofilterMode = False
With .Range("A1:G100") 'amend as required
.Autofilter
.Autofilter Field:=1, Criteria1:="YourCriteria" 'amend as required
End With
End With

You specifically turn off Autofilter first with this method before
reapplying it. It won't fail whether or not filters are applied.

Richard