Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 485
  • Last Modified:

How to determine if an Excel Table filter is on or off

I have a Command Button to Update a table connected to an Access Query.  Sometimes the table is filtered and sometimes it is not.

Currently my VBA code looks like this:

Sheets("MeditechData").Select
ActiveSheet.ShowAllData
Selection.AutoFilter
Range("C1").Select
ActiveWorkbook.RefreshAll

If the filter has been turned on this works fine.  However, if the table is not filtered, I get the error :

ShowAllData method of Worksheet class failed.

Is there a way I can set up a check to see if the table is filtered or not?

Thanks

Glen
0
GPSPOW
Asked:
GPSPOW
1 Solution
 
Rgonzo1971Commented:
Hi,

pls try

If Not ActiveSheet.AutoFilter Is Nothing Then
 ' your code
End If

Open in new window

Regards
0
 
mensmaximusCommented:
Try something like

If Not ActiveSheet.AutoFilterMode Then
    ActiveSheet.Range("c1").AutoFilter
End If

Open in new window

0
 
GPSPOWAuthor Commented:
Thanks

I added your lines to my code and it worked.

glen
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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