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.

Why so slow? With over 20,000 random numbers in A, this ran in seconds

Sub goaway()
target = InputBox("What value to go", "Delete")
Set myarray = Range("A1:A24000")
For j = 1 To myarray.Count
If (target - myarray(j)) = 0 Then
MsgBox myarray(j)
End If
Next j
End Sub

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"LarryP" wrote in message
...
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.

-or maybe-
Just sort the data and manually delete the rows in one large block.
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"LarryP"
wrote in message
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.

Anytime you delete a row or block of contiguous rows, Excel moves everything
up in its memory by the number of rows deleted, which takes time. Do that
a few thousand times, and you can take a few seconds or minutes to delete
the rows. The best way is to write your macro to

1) insert a new column
2) insert a TRUE FALSE formula into the column
3) sort the table based on that column
4) delete cells with either TRUE or FALSE, in one block.

HTH,
Bernie
MS Excel MVP

"LarryP" wrote in message
...
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.

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.

Hi,

I had the same problem recenty. Macro deleting rows worked very slow.

The problem was with page breaks. After each row delete, excel must
calculate where are the page breaks. Just go to tools - options -
view -page breaks - disable.

I hope this helps

Cheers
Pawel