Hi,

The number of rows on my xl sheet is around 40 k. i am using vba to apply autofilter inside a loop. Before applying autofilter, i use the selection.autofilter.showall.

When the code is executed, every time that the filter is reapplied, excel takes around 3 minutes to refresh. in the hintline, a "calculating" messages appears with a %.

How do we ensure that the autofilter takes less time.

Thanks

Try limiting the autofilter to just the columns you want to filter on. Also if you can sort the data by the filtering items first this will help.
The biggest tip I will give here is to switch of the Autocalculate option then do the filter and then once you have finished doing the filtering switch the calculation on again. The reason for this is because there seem to be a lot of formula in your worksheet that refer either directly or indirectly to the filtered range. By switching off the AutoRecalc Option this should infinitely sped things up, just make sure the calc is switched back on though otherwise you'll end up having reconciliation problems with your data.

Cheers

Matthew

you might try this: before your loop begins, add the following line above it: application.screenupdating = false

when your loop is done, add this: application.screenupdating = true

max