macro to delete rows

[AsquareDC]

Can someone be kind enough to help me on how to create a macro that will
delete blank rows from my worksheet that has data up to row 60,000.

 

[AsquareDC]

thanks. I have tried this on a few rows and it works perfectly. but it is
taking forever on real data of over 60,000 rows. Is this nromal

 

[Mike H]

Hi,

With calculation in manual and screenupdating off you won't make it go much
faster than it is. The only improvement you could make is specify which cells
in each row need to be empty because at the moment it checks every cell in a
row and that takes time. If it was only the first (say) 10 columns then the
speed improvement would be dramatic.

Mike

 

[Rick Rothstein]

There was an old thread I was involved in regarding the use of the Union
function for this type of operation. It was decided back then (as I recall)
that if there were lots (hundreds?) of disjointed areas involved in the
Union, that the code would become slower and slower as the union of
disjointed areas grew. Given the OP has 60,000 rows to process, the odds are
great of there being more than 100 disjointed areas involved The solution
was to do whatever operation was to be done to the union (in this case,
Delete) every 100 unions or so. Here is your code, modified to do this, (and
I also turned off the screen updating and calculations during the process to
help speed thing up a little more), this code should pretty much be the
fastest way to do the requested delete operation...

Sub macro_der()
Dim i As Long, nLastRow As Long, r As Range
Dim OriginalCalculationMode As Long
On Error GoTo Whoops
OriginalCalculationMode = Application.Calculation
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1
Set r = Rows(nLastRow + 1)
For i = nLastRow To 1 Step -1
If Application.CountA(Rows(i)) = 0 Then
Set r = Union(r, Rows(i))
If r.Areas.Count > 100 Then
r.Delete
Set r = Rows(nLastRow + 1)
End If
End If
Next
If Not r Is Nothing Then r.Delete
Whoops:
Application.Calculation = OriginalCalculationMode
Application.ScreenUpdating = True
End Sub

 

[Rick Rothstein]

60,000 thousand rows is a lot to process, so the process will be slow.
However, I believe the modification to the code by Gary''s Student that I
just posted against his message will be the fastest possible code to do what
you want (it uses a different technique than Mike's code)... give it a try.

 

[Ron de Bruin]

If you want it fast insert a column with the Counta function and filter on that column

For filter code and other delete examples see (you can add the formula column also with code if you want)
http://www.rondebruin.nl/delete.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

 

[Rick Rothstein]

There was an old thread I was involved in regarding the use of the Union

function for this type of operation.

Actually, there were two threads; here are their links...

http://www.google.com/url?url=http:...groups&usg=AFQjCNHGbBs-jxCEaFjGBfrjVdwQyXwtbA

http://www.google.com/url?url=http:...groups&usg=AFQjCNGpyyM2bVmbFnjMz108aWb1JicoTg

 

[Gary''s Student]

Thanks Rick.

I am looking at another approach. Say we go to one past the last used
column in the table (say it's column AF). In that un-used column:

=IF(COUNTA(A2:AE2)=0,1,0) and copy down

Then set AutoFilter on column AF to display only the 1's
Then delete the visible rows.

This appears to work nearly instantaneously in the worksheet. I will need
to mock up some VBA to test it further.

 

[Rick Rothstein]

I think this (assuming X is a loop counter)...

If Application.CountA(Rows(X)) = 0 Then Cells(X, LastColumn + 1) = 1

which would be the VBA equivalent to your formula...

=IF(COUNTA(A2:AE2)=0,1,0)

(although it doesn't put the 0 in when the row is non-blank) will slow
things down dramatically (due to all the interaction between code and
worksheet).