Speed up removal of all filter from all worksheets

Asked Mar 13 '23 at 11:42

Active Mar 17 '23 at 20:13

Viewed 72 times

I am working in an Excel file. My code removes the filters from every worksheet.

The process is long and Excel freezes.

Sub Auto_Open()
    Dim xWs As Worksheet
    For Each Wks In ThisWorkbook.Worksheets
    On Error Resume Next
    If Wks.AutoFilterMode Then
        Wks.AutoFilterMode = False
    End If
    Next Wks
End Sub

Edit:

Sub Auto_Open()
    Application.Calculation = xlManual 'added this line which really sepd it up
    Dim xWs As Worksheet
    For Each Wks In ThisWorkbook.Worksheets
    On Error Resume Next
    If Wks.AutoFilterMode Then
        Wks.AutoFilterMode = False
    End If
    Next Wks
    Application.Calculation = xlAutomatic
End Sub

edited Mar 17 '23 at 20:13

Community

asked Mar 13 '23 at 11:42

BlankerHans

Try [disabling automatic calculation](https://stackoverflow.com/a/47545209/17017616) while doing the work: – leosch Mar 13 '23 at 12:02
So just add `Application.Calculation = xlManual` as first line of my Sub ? – BlankerHans Mar 13 '23 at 12:11
@leosch is right , and activate automatic again at the end of your code – Foxfire And Burns And Burns Mar 13 '23 at 13:08
Yes. But you also must re-enable it when done. For test purpose you simply write `Application.Calculation = xlAutomatic` before the sub ends. – leosch Mar 13 '23 at 13:09
Could someone explain what this code really does, beside the obvious speed up? – BlankerHans Mar 13 '23 at 13:09
[VBA – Turn Automatic Calculations Off (or On)](https://www.automateexcel.com/vba/on-off-calculations/) – Foxfire And Burns And Burns Mar 13 '23 at 20:09

Speed up removal of all filter from all worksheets

0 Answers0