macro to unfilter and refilter a table

[halocore]

Hi. I've been working on a macro, and just have a last piece I could use some help with.

As part of the macro I need to to take a table that has filters applied to it, remove all the filters (or set them to "Select All"), then run the rest of the macro (copying data), and then reapply the filters as they were before running.

I'm not really sure how to proceed with this one, so any help would be greatly appreciated. Thanks.

 

[tseeker]

Dear JoeMo,

I don't have any pivot tables, but I have about 100 sheets which haven't got any filters. I'm getting stuck on this line - just like Sarkman22:

For col = 1 To UBound(filtArr(), 1)

I actually need to remove filter from 6 sheets, turn formulas to values on those 6 sheets. Adding filters on all 100+ sheets won't work for me, because other people need add new sheets on to the file making the macro not run again...

Can you please kindly help with this? I'd appreciate others assistance too.

Thanks in advance.

Also I need to Unfilter and Refilter a Specific Column with Specific Rows Filtered. This is the case with all 6 sheets.

I trust this potentially makes the Macro smaller?

Thanks.

 

[tseeker]

I've solved my problems by changing:

Removing Dim ws as Worksheet
Leaving filtArr(), curFilRng As String
Changing Set ws = ActiveSheet with Sheet3.Activate
Changing With ws with With Sheet3 (adjust as required)

It's still checking all Filters on Sheet10, but this is not an issue.

There was an issue with Filter dropdown being moved to 1 row down after running the macro. It didn't allow to manually correct the Filtering either, which was an issue for me - they needed to stay where they were originally were.

I solved this by entering a Text string on that cell where your Filter drop-down is. Please do so before using this Macro.

Sub RemoveRestoreFilter()


Dim filtArr(), curFilRng As String


Sheet3.Activate
With Sheet3
    If .FilterMode Then
        With .AutoFilter
            curFilRng = .Range.Address
            With .Filters
                ReDim filtArr(1 To .Count, 1 To 3)
                For f = 1 To .Count
                    With .Item(f)
                        If .On Then
                            filtArr(f, 1) = .Criteria1
                            If .Operator Then
                                filtArr(f, 2) = .Operator
                                filtArr(f, 3) = .Criteria2
                            End If
                        End If
                    End With
                Next f
            End With
        End With
        .ShowAllData
    End If
End With


' I changed formulas on the same sheet to values - adjust this to suit your situation
With Range("B5:B14")
    .Value = .Value
End With


'Restore original filter
With Sheet3
    .AutoFilterMode = False
    For col = 1 To UBound(filtArr(), 1)
        If Not IsEmpty(filtArr(col, 1)) Then
            If filtArr(col, 2) Then
                .Range(curFilRng).AutoFilter field:=col, _
                    Criteria1:=filtArr(col, 1), _
                        Operator:=filtArr(col, 2), _
                    Criteria2:=filtArr(col, 3)
            Else
                .Range(curFilRng).AutoFilter field:=col, _
                    Criteria1:=filtArr(col, 1)
            End If
        End If
    Next col
End With
End Sub