macro to delete rows
 

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.

macro to delete rows
 

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" wrote:

On reflection try this instead

Sub Sonic()
Dim i As Long
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
lastrow = ActiveSheet.UsedRange.Rows.Count
For i = lastrow To 1 Step -1
If WorksheetFunction.CountA(Rows(i)) = 0 Then
Rows(i).EntireRow.Delete
End If
Next i
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

Mike

"Mike H" wrote:

Hi,

Right click your sheet tab, view code and paste this in and run it

Sub Sonic()
Dim i As Long
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = lastrow To 1 Step -1
If WorksheetFunction.CountA(Rows(i)) = 0 Then
Rows(i).EntireRow.Delete
End If
Next i
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

Mike

"AsquareDC" wrote:

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.

macro to delete rows
 

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


"AsquareDC" wrote:

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" wrote:

On reflection try this instead

Sub Sonic()
Dim i As Long
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
lastrow = ActiveSheet.UsedRange.Rows.Count
For i = lastrow To 1 Step -1
If WorksheetFunction.CountA(Rows(i)) = 0 Then
Rows(i).EntireRow.Delete
End If
Next i
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

Mike

"Mike H" wrote:

Hi,

Right click your sheet tab, view code and paste this in and run it

Sub Sonic()
Dim i As Long
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = lastrow To 1 Step -1
If WorksheetFunction.CountA(Rows(i)) = 0 Then
Rows(i).EntireRow.Delete
End If
Next i
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

Mike

"AsquareDC" wrote:

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.

macro to delete rows
 

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 (MVP - Excel)


"Gary''s Student" wrote in message
...
Sub macro_der()
Dim i As Long, nLastRow As Long
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1
Set r = Rows(nLastRow + 1)
For i = 1 To nLastRow
If Application.CountA(Rows(i)) = 0 Then
Set r = Union(r, Rows(i))
End If
Next
r.Delete
End Sub

--
Gary''s Student - gsnu200847


"AsquareDC" wrote:

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.

macro to delete rows
 

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.

--
Rick (MVP - Excel)


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

On reflection try this instead

Sub Sonic()
Dim i As Long
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
lastrow = ActiveSheet.UsedRange.Rows.Count
For i = lastrow To 1 Step -1
If WorksheetFunction.CountA(Rows(i)) = 0 Then
Rows(i).EntireRow.Delete
End If
Next i
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

Mike

"Mike H" wrote:

Hi,

Right click your sheet tab, view code and paste this in and run it

Sub Sonic()
Dim i As Long
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = lastrow To 1 Step -1
If WorksheetFunction.CountA(Rows(i)) = 0 Then
Rows(i).EntireRow.Delete
End If
Next i
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

Mike

"AsquareDC" wrote:

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.

macro to delete rows
 

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




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

On reflection try this instead

Sub Sonic()
Dim i As Long
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
lastrow = ActiveSheet.UsedRange.Rows.Count
For i = lastrow To 1 Step -1
If WorksheetFunction.CountA(Rows(i)) = 0 Then
Rows(i).EntireRow.Delete
End If
Next i
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

Mike

"Mike H" wrote:

Hi,

Right click your sheet tab, view code and paste this in and run it

Sub Sonic()
Dim i As Long
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = lastrow To 1 Step -1
If WorksheetFunction.CountA(Rows(i)) = 0 Then
Rows(i).EntireRow.Delete
End If
Next i
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

Mike

"AsquareDC" wrote:

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.

macro to delete rows
 

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:/...BfrjVdwQyXwtbA

http://www.google.com/url?url=http:/...z108aWb1JicoTg

--
Rick (MVP - Excel)

macro to delete rows
 

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.
--
Gary''s Student - gsnu200847


"Rick Rothstein" wrote:

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 (MVP - Excel)


"Gary''s Student" wrote in message
...
Sub macro_der()
Dim i As Long, nLastRow As Long
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1
Set r = Rows(nLastRow + 1)
For i = 1 To nLastRow
If Application.CountA(Rows(i)) = 0 Then
Set r = Union(r, Rows(i))
End If
Next
r.Delete
End Sub

--
Gary''s Student - gsnu200847


"AsquareDC" wrote:

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.

macro to delete rows
 

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

--
Rick (MVP - Excel)


"Gary''s Student" wrote in message
...
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.
--
Gary''s Student - gsnu200847


"Rick Rothstein" wrote:

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 (MVP - Excel)


"Gary''s Student" wrote in
message
...
Sub macro_der()
Dim i As Long, nLastRow As Long
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1
Set r = Rows(nLastRow + 1)
For i = 1 To nLastRow
If Application.CountA(Rows(i)) = 0 Then
Set r = Union(r, Rows(i))
End If
Next
r.Delete
End Sub

--
Gary''s Student - gsnu200847


"AsquareDC" wrote:

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.