ShowAllData method of Worksheet class failed

excel vba autofilter
Kris Van den Bergh picture Kris Van den Bergh · Aug 14, 2013 · Viewed 152.2k times · Source

I notice my VBA script doesn't work when there's an autofilter already on. Any idea why this is?

    wbk.Activate
    Set Criteria = Sheets("Sheet1").Cells(i, 1)

    Set rng = Sheets("Sheet1").Range(Cells(i, 2), Cells(i, 4))

    wb.Activate
    If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData 'remove autofilter, but it crashes on this line

    Selection.AutoFilter

    Range("$A$1:$BM$204").AutoFilter Field:=2, Criteria1:=Criteria.Value

    rng.Copy

    Range("$BC$2:$BE$204").SpecialCells(xlCellTypeVisible).PasteSpecial

Many thanks

Answer

Aaron picture Aaron · Dec 14, 2013

AutoFilterMode will be True if engaged, regardless of whether there is actually a filter applied to a specific column or not. When this happens, ActiveSheet.ShowAllData will still run, throwing an error (because there is no actual filtering).

I had the same issue and got it working with 

If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
End If

This seems to prevent ShowAllData from running when there is no actual filter applied but with AutoFilterMode turned on.

The second catch Or ActiveSheet.FilterMode should catch advanced filters

Related questions

Using string array as criteria in VBA autofilter

I've searched other posts and found similar issues but nothing that could help me specifically. I'm trying to take an array of strings and use it as a filter criteria. It's tricky because the array is created by a function …

Read More
Autofilter with column formatted as date

I am using an AutoFilter with VBA in Excel that works for regular filters, but not a column formatted as date. I can filter it manually. If I run my code, it filters nothing but when I check the filter …

Read More
Excel VBA to check autofilter for data

I need help checking for autofiltered rows not including the header. I want it to give a message box "No records found." then exit sub or continue with copy paste if there are rows beyond the header row. I know …

Read More