ShowAllData method of Worksheet class failed

autofilter, excel, vba

Solution

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

Problem

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

Original source