Misteriously slow filtering in a structured table

[laooglee]

Hi all.
Have been seeking for a while around the forum and didn't found what I need.

I have a relatively small structured table (about 600 rows x 10 columns) and it takes toooo long to auto-filter it by any column (about one minute to be processed). It doesn't have any external links, though some other tables are related to this table.

What's more interesting - each time I make any significant change in the table (like deleting/adding a column or clearing table format) it starts to filter in a mere ms. But after I save, close and open it again, the table slows down like hell

 

[Gingertrees]

Welcome to the board. If you have any calculations (vlookups, sumifs, etc.) that are in any of these cells, it is recalculating them each time you autofilter. You can go to your Formulas tab, select Calculation Options - manual beforehand. That might help.

Otherwise, it could be a screenupdating issue.

 

[laooglee]

Welcome to the board. If you have any calculations (vlookups, sumifs, etc.) that are in any of these cells, it is recalculating them each time you autofilter. You can go to your Formulas tab, select Calculation Options - manual beforehand. That might help.

Otherwise, it could be a screenupdating issue.

Forgot to mention. File is already on the manual mode.

May you give a bit details about screenupdating issue?

 

[Gingertrees]

Screenupdating is what causes flickering as your view is updated while code or formulas are running.
You could add a quick subroutine to shut off screenupdating when you want to autofilter. (And then here's one to turn it back on afterwards.)
Click on developer tab, Visual Basic, then insert a module. Paste in this code:

Sub turnOFFscrnupdt()
Application.Screenupdating = False
'//use this before you change your filters
End Sub

Sub turnONscrnupdt()
Application.Screenupdating = True
'//use this after you're done filtering
End Sub

Then you can run the needed macro from the Macros button in Developer tab.

 

[laooglee]

to shut off screenupdating

Funny, it's just doesn't let me do it. In any other file it does, but here doesn't..

I execute Application.Screenupdating = False, but when I check Screenupdating status, by printing Application.Screenupdating in the Immediate window it just returns me "True" again and again

 

[laooglee]

You will be laughing, but Application.EnableEvents is what have helped me (in the same manner - False, then True).
And now it becomes even more mysterious, since there's not a macro, which would use event handler except a little "Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)" subroutine.

P.S. And what a crappy rule - not to edit post after 10 minutes after posting : -\