Fast Way to Delete Rows from Big Spreadsheet (Using VBA)

[LarryP]

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.

 

[Bernard Liengme]

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

 

[Jim Cone]

-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" <[email protected]>
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.

 

[Bernie Deitrick]

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

 

[Jim Thomlinson]

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...

 

[p.jankiewicz]

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