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.

 

[JoeMo]

Try this (untested). There's a comment line where you can insert your copying data code:

Sub RemoveRestoreFilter()
Dim ws As Worksheet, filtArr(), curFilRng As String
Set ws = ActiveSheet
With ws
    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
'Rest of code here

'Restore original filter
With ws
    .AutoFilterMode = False
    For col = 1 To UBound(filtArr(), 1)
        If Not IsEmpty(filtArr(col, 1)) Then
            If filtArr(col, 2) Then
                w.Range(currfilrng).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

 

[Rastaman]

Maybe I'm missing something, but if using Excel 2007 can't you just do this?

Sub Macro1()

    Worksheets("Sheet1").ShowAllData
    
End Sub

Rick

 

[JoeMo]

Maybe I'm missing something, but if using Excel 2007 can't you just do this?

Sub Macro1()

    Worksheets("Sheet1").ShowAllData
    
End Sub

Rick

You can if that's all you want to do, but the request was to remove the filter so some copying could be done and then RESTORE the original filter programatically.

 

[Sarkman22]

I realize this post is 6 years old......but felt like I wanted to give you a shout out for posting this! I was trying to find a code to auto filter and re-filter based on whatever criteria was applied. This worked like a charm! Much obliged JoeMo. Much obliged.

 

[JoeMo]

I realize this post is 6 years old......but felt like I wanted to give you a shout out for posting this! I was trying to find a code to auto filter and re-filter based on whatever criteria was applied. This worked like a charm! Much obliged JoeMo. Much obliged.

Thanks for taking the time to comment. When I looked at the code (which I had never tested) to refresh my memory I noticed an error which I'll take this opportunity to correct. In the code in post #2:

change this bit in the part that restores the original filter

w.Range(currfilrng).AutoFilter field:=col, _

to this

.Range(curfilrng).AutoFilter field:=col, _

That is, just remove the two characters in bold red.

 

[Sarkman22]

Awesome many thanks again!! For some reason it worked prior to making the changes you listed. However I made them anyway for good measure. Also strange to me- and I'm posting in case your interested but no reply necessary:

I did not mention that this is taking place in a power pivot table. I was running into an issue where I could run this code once with success. However if the very next thing I did (before clicking ANYTHING) was run the exact code again it broke at this line:

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

BUT- if I clicked on a cell inside the power pivot table range PRIOR to running the code a second time, it works great! My initial code finished with a "Range A1 Select" and cell A1 is OUTSIDE the power pivot table range. I changed the code to finish by selecting a random cell INSIDE the power pivot range, and it appears to have worked. I can keep running the code without error as many times as I want. This is important as I want to be able to click a button and just run the code many times in a row.

Just thought you might be curious to know that. Thanks a MILLION for your help- I am extremely grateful.

Thanks for taking the time to comment. When I looked at the code (which I had never tested) to refresh my memory I noticed an error which I'll take this opportunity to correct. In the code in post #2:

change this bit in the part that restores the original filter

w.Range(currfilrng).AutoFilter field:=col, _

to this

.Range(curfilrng).AutoFilter field:=col, _

That is, just remove the two characters in bold red.

 

[JoeMo]

Awesome many thanks again!! For some reason it worked prior to making the changes you listed. However I made them anyway for good measure. Also strange to me- and I'm posting in case your interested but no reply necessary:

I did not mention that this is taking place in a power pivot table. I was running into an issue where I could run this code once with success. However if the very next thing I did (before clicking ANYTHING) was run the exact code again it broke at this line:

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

BUT- if I clicked on a cell inside the power pivot table range PRIOR to running the code a second time, it works great! My initial code finished with a "Range A1 Select" and cell A1 is OUTSIDE the power pivot table range. I changed the code to finish by selecting a random cell INSIDE the power pivot range, and it appears to have worked. I can keep running the code without error as many times as I want. This is important as I want to be able to click a button and just run the code many times in a row.

Just thought you might be curious to know that. Thanks a MILLION for your help- I am extremely grateful.

Glad it helps. Just FYI- unless at least one of the initial filters applied prior to running the code uses an operator with two criteria applied to the same column, the bit that had the error would be skipped and you would not get any error from that line.

 

[Sarkman22]

Good to know thank you.

Glad it helps. Just FYI- unless at least one of the initial filters applied prior to running the code uses an operator with two criteria applied to the same column, the bit that had the error would be skipped and you would not get any error from that line.

 

[tseeker]

Glad it helps. Just FYI- unless at least one of the initial filters applied prior to running the code uses an operator with two criteria applied to the same column, the bit that had the error would be skipped and you would not get any error from that line.

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.