Restore all Filtered Data

[drbrow592]

I have used the following UDF to allow users to Filter data, but I would like one Button to restore allData and Show "All" in Header row. Is this possible?

I do have a Button Macro that restores allData but will not Show "All" in header.

Function FilterCrit(Rng As Range) As String
Dim Filter As String
Filter = "All"
With Rng.Parent.AutoFilter
If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
With .Filters(Rng.Column - .Range.Column + 1)
If Not .On Then GoTo Finish
Filter = .Criteria1
Select Case .Operator
Case xlAnd
Filter = Filter & " AND " & .Criteria2
Case xlOr
Filter = Filter & " OR " & .Criteria2
End Select
End With
End With
Finish:
FilterCrit = Filter
End Function

 

[Von Pookie]

To turn all filters off, you just use Application.ShowAllData. Assuming your above code is handling the "header" display you're talking about, just have it show whatever you want. If not--I don't really know what you mean when referring to the header.

 

[drbrow592]

Kristy,

Where/How is that Application applied to a button??

 

[Von Pookie]

um...you just put that line in a sub and assign the macro to a button.

It seems they changed it on me in Excel 2007, though. For 2007, you specify the sheet:

Sub ShowAll()
ActiveSheet.ShowAllData
End Sub

Again, I'm not sure what you are referring to by "header display" or even where that is, so in general: wherever you want to display the filter criteria, just write a line to put that in there. For example:

Sub ShowAll()
With ActiveSheet
    .ShowAllData
    .Range("A1") = "All"
End With
End Sub