auto filter very slow to return results

**martindwilson** · 02-12-2009, 05:30 PM

i have a work book of 12 sheets , the two main sheets 1+2 have 5000+ rows.
by 30 columns
each row column b is a site number in ascending order(up to 4 for each site) so auto filter select = to site 5
about 10 seconds later i get the result of 3 sites
try site 846 30 odd seconds later up pops the result. so i copied the sheet to a new work book and tried and it's nigh on instantaneous.
next step
copied sheet paste special values into new sheet in same original workbook
and tried filter on that still slow as ever,
thing is this book around 8meg and i cant for the life of me work out why
ive cleared excessive formats and reset last cell on each sheet.
there is a vlookup from sheet 1 to 2 for each site to return serial numbers.
i have tried it in vba by turning autocal off/on but still no difference.
any ideas if anything else could cause this?

**DonkeyOte** · 02-12-2009, 05:35 PM

The interesting this is you say you've tried on manual calc... AutoFilter is a volatile action so if you had lots of volatile functions in your model changing the Auto Filter would cause them all to recalc... however not on manual calc... do you have lots of Conditional Formatting rules (also Volatile)

**martindwilson** · 02-12-2009, 05:46 PM

ah conditional formats, haven't checked that but i did notice there were alot of pretty coloured texts an i doubt if the bloke who sent it to me did all that manually. thanxs for the idea, i'll have a look tomorrow at work(a. since it's getting to me and b. they are paying me while i do it lol)

**martindwilson** · 02-16-2009, 09:08 AM

donkeyote had a day off friday so i couldnt look but
ok i have totally rebuilt work book, yes there were loads of cf's and over use of = today() ,and changed all lookups to a1:a5000 rather than a:a
its still slow though. the sheet goes fronm a1:bz5200
a solutions seems just copy the columns i needed (as the sheet is just too large)
to spare sheet called data!
then used the auto filter on that
now i have trouble with the code that does this.
i'll come back if its not fixed!
GRRR I HAD PUT A LINE OF CODE IN WRONG PLACE !!!!
just by copying the cols i needed (which incidently removed the volatile cf's as donkeyote pointed out ) , it works like a charm