Navin
Navin

Reputation: 51

How to deal with autofilter using vba

enter image description here

I am having trouble with below line codes at line ActiveSheet.ShowAllData because at times my worksheet has the auto filter on and at times off. Is there a way to cater for this ??

  enter image description here

Upvotes: 0

Views: 73

Answers (2)

Gary's Student
Gary's Student

Reputation: 96781

Consider:

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

Note that this does not actually remove autofilters, only removes any de-selections. It will also:

  • not barf if all data is already showing
  • not barf if filtering not is present.

Upvotes: 1

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19837

It's so much easier if you paste the code straight into your question rather than display a picture - can't copy and paste a picture into the VBE.

You need to check if anything's filtered before clearing the filter:

If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData

Upvotes: 0

Related Questions