Data Filters Running Very Slow

Hi,

I have an excel sheet with about 3,000 products listed; there are 26 items of data listed against each.

I've been using Data Filters to manipulate the info, but my worksheet has suddenly started running incredibly slowly - up to 7 minutes to implement a simple filter selection.

There was no problem until I made a couple of changes last week:

1. Adding a simple pivot table
2. Using an Index column plus some linked formulae to extract a list of suppliers from the product list.

I assumed that it may be the INDEX and related formulae or the pivot table that were causing the Data Filters to run slow, so I deleted them, but to no effect. I've even gone so far as to copy the raw data into a new sheet, but it's still running incredibly slowly when I try to filter. I am convinced that the problem is related to some hidden legacy of the table or functions, as it only started once I'd added these, but I don't know if this is plausible in reality and if so how to get rid of it.

Any suggestions appreciated.

-Joe

Toggling Row Visibility is a Volatile action (for why ? think SUBTOTAL function).
Given the above any Volatile formulae you have will recalculate as and when Filtering is applied.
If you have any Arrays / SUMPRODUCTs that are Volatile the impact will be amplified as they are generally slow to calculate.
Excessive use of Conditional Formats and Named Ranges can also impact peformance.

Got any conditional formatting?

There are 19 shapes on the sheet that seem to defy selection.

I ran this, and then it filtered instantly:

Please Login or Register  to view this content.

and now it is my turn to be too slow...

Remove the AutoFilter.

Adding a Macro to a Code Module
1. Copy the code from the post
2. Press Alt+F11 to open the Visual Basic Editor (VBE)
3. From the menu bar in the VBE window, do Insert > Module
4. Paste the code in the window that opens
5. Close the VBE to return to Excel

Then do Alt+F8, select x in the drop-down, and press Run.

Then restore the Autofilter and filter away.

Failed to notice that, sorry.

No VBA on Macs, and I've never used one, so I don't have a different suggestion.

Originally Posted by shg

Failed to notice that, sorry.

No VBA on Macs, and I've never used one, so I don't have a different suggestion.

I thought there was VBA support on versions prior to XL 2008 but in 2004 is VB5 based rather than VB6 ... where are the Mac lovers when you need one Romperstomper / Mikerickson etc... ?

I found this blog by Nate which may be of interest: https://msmvps.com/blogs/nateoliver/...xperience.aspx

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.