Clear filters in VBA

[vavs]

I have a spreadsheet that goes through several macros in order to update. One of the macros did not complete properly due to a filter that was applied to the sheet. Is there a way to add this to my first macro in the string, whereby I query each sheet and clear the filters before I start?

I have tried this and I get a compile error:

Rows(1). Select
Selection.Autofilter
Selection.Autofilter

I don't know how to turn it on and then off.

 

[hiker95]

vavs,

This may help you get started in the right direction.

Dim wks As Worksheet
   For Each wks In Me.Worksheets
        wks.AutoFilterMode = False
    Next wks

Or, toggle AutoFilter on/off:

Sub MyAutoFilter()
With ActiveSheet
  If .FilterMode Then
    .AutoFilter
  Else
    .UsedRange.AutoFilter
  End If
End With
End Sub

 

[Jon von der Heyden]

Try;

ActiveSheet.ShowAllData

</pre>

 

[vavs]

Thanks for the help. I used the showalldata solution.

 

[ParamRay]

.
.

This solves the original problem:

Sub ToggleAutoFilter()

    With ActiveSheet
        If .AutoFilterMode Then
            .AutoFilterMode = False
        Else
            .Range("I1").CurrentRegion.AutoFilter _
                Field:=22, _
                Criteria1:="<>"
        End If
    End With
    
End Sub

 

[ParamRay]

.
.

Also, see below link for passing arguments by value and by reference.

Passing Arguments by Value and by Reference (Visual Basic)

 

[BonnieM]

I already have the following code in MY Workbook - where should I place this line? Where I have it now is throwing an error.

(you can see I tried another solution, but it didn't work, so I have it remarked out)

Private Sub Workbook_Open()


'Dim sh As Variant
'For Each sh In Worksheets
'If sh.FilterMode Then sh.ShowAllData
'Next


On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
On Error GoTo 0
RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0)
Application.OnTime RunWhen, "SaveAndClose", , True
End Sub




Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
On Error GoTo 0


ActiveSheet.ShowAllData


'Dim WS As Worksheet
' For Each WS In Worksheets
' WS.AutoFilterMode = False
' Next WS
ThisWorkbook.Save


End Sub




Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
On Error GoTo 0
RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0)
Application.OnTime RunWhen, "SaveAndClose", , True
End Sub




Private Sub Workbook_SheetSelectionChange(ByVal sh As Object, _
ByVal Target As Range)




On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
On Error GoTo 0
RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0)
Application.OnTime RunWhen, "SaveAndClose", , True




End Sub

 

[atrus]

Try;

ActiveSheet.ShowAllData

this works great for me as long as the sheet is filtered but if the sheet happens to not be filtered at the time I run the macro it throws a "run-time error '1004': ShowAllData method of Worksheet class failed" what would be a good way to add a check to see if it is filtered, run this if it is but skip it if it is not filtered?

 

[leighmetcalfe]

Try the below, it works a treat for me

If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.ShowAllData