• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 867
  • Last Modified:

How to clear an autofilter in VBA

Hi Experts, this should be an easy question, but I can't seem to find an answer.  I need a line or two of code that will clear all the autofilters in a given worksheet.  I have a sheet for which I refresh data on a regular basis, but if I have any filters set (which I often do) it screws up my refresh.  I can toggle the auto filters off then back on - but this seems kind of kludgy.

-Thanks,
0
JamesCbury
Asked:
JamesCbury
  • 4
  • 4
1 Solution
 
cyberkiwiCommented:
If ActiveSheet.AutoFilterMode = True Then
ActiveSheet.AutoFilterMode = False
End If
0
 
cyberkiwiCommented:
Replace ActiveSheet with Sheets("sheetname")...
0
 
JamesCburyAuthor Commented:
thanks, that works to turn off the autofilters, but I'm just looking to reset them; essentially clicking 'select all' in each column.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
cyberkiwiCommented:
If ActiveSheet.FilterMode Then
    ActiveSheet.ShowAllData
End If
0
 
patrickabCommented:
Try,

Sub see_all()
    Selection.AutoFilter Field:=1
End Sub
0
 
JamesCburyAuthor Commented:
perfect, thanks
0
 
JamesCburyAuthor Commented:
Actually... this throws an error in the event that there are no filters set.  anyway around this other than an error handler?
0
 
cyberkiwiCommented:
It works ok in Excel 2007 with or without filters, but you could try this variant:

If ActiveSheet.AutoFilterMode = True Then
    ActiveSheet.ShowAllData
End If
0
 
JamesCburyAuthor Commented:
sorry, I should have clarified.  It's throwing an error when I have autofilters on, but nothing is filtered (I get this it I try to run the sub two times in a row - the first time shows all, the second time throws an error).
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now