Excel Window Freezes after Clearing All Filters

[Seba Robles]

Hi all,

So I have a file that when I hit the CLEAR ALL FILTERS button it makes my Excel window hang/freeze.

I can still click around, save the file, etc but without any visual reaction (if that makes any sense) - I can't really see what I'm doing.

Any idea why this happens or how can I fix it?

I only have 1 filtered column, not multiple. If I remove/clear the filter directly from the column the problem doesn't seem to occur, but this is a shared file and that's not a viable option for when multiple people use it.

I have a Dell Inspiron i5 64bit OS running Office 365 32 bit. Not sure if this info helps.

Thanks in advance!

 

[Kamolga]

If the file contains a lot (hundreds thousands lines) of data, those bugs can happen. It can also happen if a macro is linked to the clear all filters (rare but very often we can use application.screenupdating=false to gain speed and can forget to put it back on).

Normally computer I5 is way strong enough to handle excel files but if other users do not have the problem, you would have a lead to the cause. Saving the file on a key and opening it on another computer can help if other users use the same computer.

 

[MichelExcel]

found this answer on microsoft community page, go to last comment.

unfreeeze panes and refreeze to get rid of the bug.

https://answers.microsoft.com/en-us/msoffice/forum/all/excel-freeze-when-removing-active-filter-from/f54b3e0f-bc82-4614-a553-80e2ee1872d8

 

[tjadam61]

I had the same issue in a very complex workbook I am creating. Clearing filters one by one is fine but using the clear filters button (even if there was only one filter applied) would lock up my workbook. I got around it by creating a clear filter macro in VBA using the following code:
Sub ClearFilters()
Dim plannerSheet As Worksheet
Dim lastRowPlanner As Long

Set plannerSheet = ThisWorkbook.Sheets("Planner")
lastRowPlanner = plannerSheet.Cells(plannerSheet.Rows.Count, "A").End(xlUp).Row
plannerSheet.Range("A3:BO" & lastRowPlanner).AutoFilter
plannerSheet.Range("A3:BO" & lastRowPlanner).AutoFilter
End Sub
plannerSheet is the variable I use to define my worksheet and last row planner finds the last row in the table I am clearing the filters of. A3:BO is the range of the values in my table (Not including Headers). This code simply disables and reenables the autofilter feature, clearing out filters in the process. If you have autofilter disabled, it will leave it disabled. Depending on your workbook, you may need to add an unprotect and reprotect statement around the code

 

[franticman]

Thanks!

 

[sam89665]

Hi all, I used to have this problem all the time, then it seemed to resolve itself, but within the last few weeks it's returned - maybe due to a Windows update? Does anyone have a solution other than not using Freeze Panes or using VBA to clear all the filters? Thanks

 

[perthling]

found this answer on microsoft community page, go to last comment.

unfreeeze panes and refreeze to get rid of the bug.

https://answers.microsoft.com/en-us/msoffice/forum/all/excel-freeze-when-removing-active-filter-from/f54b3e0f-bc82-4614-a553-80e2ee1872d8

This didn't work for me unfortunately. Using exclusively formatted tables and Power Queries, Excel consistently going into a mode where the window display will not refresh, always as the result of using 'Clear All Filters'. I unfroze panes, saved, exited; reopened, froze panes, saved exited; reopened and first attempt to 'Clear All Filters' threw the display into non-refresh mode. I'll go with the VBA solution if a full repair doesn't fix it.