VBA code to unfilter data not working correctly

SKV

Active Member
Joined
Jan 7, 2009
Messages
255
I have a code to clear the filter (copied from another code I created last year and have been working fine all the time).

ISSUE: The code is not consistently detecting the filters. Below is the code.

If Worksheets("ABC").FilterMode = True Then
ActiveSheet.ShowAllData
End If

When I run my code step by step,when the code reached the IF statement code line for FilterMode, for some reason it directly goes to the ENF if line inspite of the fact that the worksheet has filters. Sometime it catches though but not consistent all the time.

What shall I do that it detects the filter all the time.

Please advice.
Thanks in advance
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Can you please provide a sample file, perhaps through some file sharing service?
The file should be clean and simple yet highlight the issue you have.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,557
Office Version
  1. 365
Platform
  1. Windows
If Worksheets("ABC").FilterMode = True Then
ActiveSheet.ShowAllData
End If

.. inspite of the fact that the worksheet has filters.
Not sure that this could be your issue but your code refers to worksheets twice (red code). Is the active sheet always "ABC" (or always NOT "ABC") when this code gets to these lines?

You then say "the worksheet" has filters. Which worksheet - "ABC" or ActiveSheet?

In any case here's another method that may suit. Adjust ActiveSheet to Woksheets("ABC") if required.
Code:
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0
 

SKV

Active Member
Joined
Jan 7, 2009
Messages
255
Wiji and Peter - Thanks for your reply.

Wiji - I can share the file (let me find a way) however please note that this error is random. Sometimes the code works perfectly and sometime it errors out. That is what intriguing me more.

Peter - Good catch. However my issue gets stuck on the first line only. That is even though I have filters on my data on data on sheet (ABC), when the code reads the statement If Worksheets("ABC").FilterMode = True, it somehow reads it as False and the directly jumps to the endif statement. But sometimes it reads it correctly.

Any Advice.
 

SKV

Active Member
Joined
Jan 7, 2009
Messages
255

ADVERTISEMENT

m
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,557
Office Version
  1. 365
Platform
  1. Windows
.. even though I have filters on my data ..
Just to be clear, FilterMode only detects if one or more columns actually has a filter criteria set. If your data has AutoFilter arrows but every column is set to "Select All" then FilterMode returns False.

Could that be what is happening for you?
Did you try my alternative code in post #3 and did it do what you want?


Here are two basic tests.
The first determines whether or not there is an AutoFilter set up somewhere on the active sheet (but not whether any of the columns are actually filtered).
The second determines if any columns actually have a filter applied.
Code:
Sub Test_AutoFilter_Existence()
  If ActiveSheet.AutoFilterMode Then
    MsgBox "AutoFilter exists for the range " & ActiveSheet.AutoFilter.Range.Address(0, 0)
  Else
    MsgBox "No autofilter is set up on the active sheet"
  End If
End Sub

Sub Test_AutoFilter_Applied()
  If ActiveSheet.FilterMode Then
    MsgBox "AutoFilter is being applied"
  Else
    MsgBox "AutoFilter is not applied or does not exist"
  End If
End Sub
 

cstoff

New Member
Joined
Nov 21, 2014
Messages
1
Hi. I know this is late coming. However, just in case...

Had the same problem. In order for the check to work, you also need the activecell to be in the filtered table. If it is not, even though there is a filtered table on the sheet the check will return false
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,557
Office Version
  1. 365
Platform
  1. Windows
Hi. I know this is late coming. However, just in case...

Had the same problem. In order for the check to work, you also need the activecell to be in the filtered table. If it is not, even though there is a filtered table on the sheet the check will return false
Welcome to the MrExcel board!

Whilst you are late to the thread, your post has triggered more information that may help other readers, so I'll add some further comments.

Your post refers to a table in the worksheet but a worksheet can contain an AutoFiltered range without that range being a table.

The original poster (OP) made no mention of having a table in their worksheet. However, there very well may have been and, if so, could explain why the OP was getting inconsistent results (depending on where the ActiveCell was) when the code ran.

I disagree that in general you need to have the ActiveCell in the table, see code below.



A worksheet can have at most one standard range that has an AutoFilter attached to it. It can also have any number of tables and these can all have filters associated with them and any combination of those filters may be applied or not applied.
Here is an attempt to check the ActiveSheet for any standard AutoFilter or any Table AutoFilter being applied.
This is not my strongest vba area so any constructive criticisms or comments welcome.

Rich (BB code):
Sub Test_Filters()
  Dim loTbl As ListObject
  Dim msg As String
  
  msg = "Item" & vbTab & "AutoFilter" & vbLf
  
  'Check standard AutoFilter
  msg = msg & "Std AF" & vbTab
  If ActiveSheet.AutoFilterMode Then
    If ActiveSheet.FilterMode Then
      msg = msg & "Applied"
    Else
      msg = msg & "Not applied"
    End If
  Else
    msg = msg & "Doesn't exist"
  End If
  
  'Now check any tables
  If ActiveSheet.ListObjects.Count = 0 Then
    msg = msg & vbLf & "Tables" & vbTab & "None exist"
  Else
    msg = msg & vbLf & vbLf & "Tables"
    For Each loTbl In ActiveSheet.ListObjects
      msg = msg & vbLf & loTbl.Name & vbTab
      If loTbl.AutoFilter.FilterMode Then
         msg = msg & "Applied"
      Else
        msg = msg & "Not applied"
      End If
    Next loTbl
  End If
  
  'Display results
  MsgBox msg
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,174
Members
417,130
Latest member
Darion2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top