VBA code to unfilter data not working correctly

[SKV]

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

 

[wigi]

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]

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.

On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0

 

[SKV]

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]

m

 

[Peter_SSs]

.. 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.

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]

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]

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.

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