Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
| Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 2nd, 2005, 02:07 PM
Registered User
 
Join Date: Feb 2005
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Autofilter taking too much of time

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

 
Old March 4th, 2005, 09:24 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: Bournemouth, Dorset, United Kingdom.
Posts: 180
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


 
Old March 4th, 2005, 05:43 PM
Authorized User
 
Join Date: Feb 2005
Location: , , Netherlands.
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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




Similar Threads
Thread Thread Starter Forum Replies Last Post
Datagrid taking huge render time gunjan.sh ASP.NET 2.0 Basics 1 June 4th, 2008 07:40 AM
View returns huge data,taking more time gunjan.sh SQL Server 2005 15 April 9th, 2008 05:51 AM
Remove Autofilter yogeshyl Excel VBA 4 June 11th, 2007 06:59 AM
MTOM taking more time than base64 method shahzadgodil .NET Web Services 0 February 27th, 2007 02:51 AM
AutoFilter 5 columns at once Pindacko Excel VBA 0 April 26th, 2006 05:33 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.