• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 977
  • 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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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