Typically the slow part is the delete for 2 reasons. One it generates a
calculation and the other is that all rows need to be moved up. To speed that
up typically you want to create a single large area to be deleted instead of
just doing the rows one at a time.

Look at the union function to union ranges together.

In your source data do you have numbers and text mixed together. Taht could
explain your mysterious autofilter issue...
--
HTH...

Jim Thomlinson


"LarryP" wrote:

Excel 2003, big spreadsheet, ~20000 rows, Col. B contains text values like
"1000", "1070", and "1120". Users sometimes want to quickly delete all the
rows except those with one specific value in that Column. I've tried a
couple approaches and run into (different) grief in each case. With a "For
each row in used range" looping solution, it runs very slowly with that many
rows. With a not-equal-to Autofilter, then deleting all result rows, it's
much faster but doesn't always filter correctly with Criterion:="<1000" for
some reason, haven't been able to figure out why. (Clarification: it hiccups
no matter what value is specified, not just 1000.) When it works it's just
what I want, but the next time with no apparent rhyme or reason it'll leave
in the specified value, and poof!, the user winds up with a completely blank
spreadsheet.

Any thoughts? In particular, is there any way BESIDES these two that would
run really fast? I'm constantly finding out about cool built-ins in Excel
that I wasn't aware of, so I'm hoping there's one for this situation.