CODEWITHSUNDEEP
excelvba
atomoutside
atomoutside

Reputation: 189

VBA check sheet for filtered data

Is there any way to check if sheets have any filtered data (If there is filtered data, then clear filter, else do nothing)? I have this code here, but I don't know how to write second part:

Sub ProtectAll()

Dim wSheet          As Worksheet

For Each wSheet In Worksheets

With wSheet
If .AutoFilterMode Then
            .ShowAllData
            .Cells.Locked = True
            .Cells.FormulaHidden = False
           '.Range(wSheet.Cells(12, 1), wSheet.Cells(12, 18)).AutoFilter
           '.Protect Password:=Pwd, AllowFiltering:=True

ElseIf ??? Then
End If
End With

Next wSheet
End Sub

Upvotes: 3

Views: 9550

Answers (2)

Alejandro Teruel Arbues
Alejandro Teruel Arbues

Reputation: 11

I think the best way is using .FilterMode

If Sheets("NameSheet").FilterMode Then Sheets("NameSheet").ShowAllData

This code removes filtered data the sheet have and show all data keeping autofilters.

Upvotes: 1

gfuellerer
gfuellerer

Reputation: 157

This code removes all autofilters from ActiveSheet

ActiveSheet.Autofilter.Range.Autofilter

Afterwards you can reset the filter (no criteria selected) by defining an appropriate range

ActiveSheet.Range("A1:B1").Autofilter

Upvotes: 1

Related Questions