Slow Delete Blank Rows Macro

M

Monk

I am using the code below to delete blank rows from up to 50,000 rows of
data. The data can be between 20,000 and 50,000 rows. It works fine however
it takes about 10 mins to complete. Any suggestions on improved code that
could complete the task quicker?

Sub Delete_Rows_Empty()
Application.Calculation = xlManual
Application.ScreenUpdating = False
Dim Rng As Range, ix As Long
Dim csht As Long
Set Rng = Range("p2:p50000")
For ix = Rng.Count To 1 Step -1
If Trim(Application.Substitute(Rng.Item(ix).Text, _
Chr(160), Chr(32))) = "" Then
Rng.Item(ix).EntireRow.Delete
End If
Next
done:
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
End Sub
 
J

Jim Cone

Made some changes...
'--
Sub Delete_Rows_EmptyR1()
Dim Rng As Range
Dim ix As Long
Application.Calculation = xlManual
Application.ScreenUpdating = False
Set Rng = Cells(Rows.Count, "P").End(xlUp)
Set Rng = Range("P2", Rng)
' MsgBox Rng.Address
For ix = Rng.Count To 1 Step -1
If Len(Trim$(VBA.Replace(Rng(ix).Value, Chr$(160), _
Chr$(32), 1, -1, vbTextCompare))) = 0 Then
Rng(ix).EntireRow.Delete
End If
Next
done:
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
End Sub
'--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Monk"
wrote in message
I am using the code below to delete blank rows from up to 50,000 rows of
data. The data can be between 20,000 and 50,000 rows. It works fine however
it takes about 10 mins to complete. Any suggestions on improved code that
could complete the task quicker?

Sub Delete_Rows_Empty()
Application.Calculation = xlManual
Application.ScreenUpdating = False
Dim Rng As Range, ix As Long
Dim csht As Long
Set Rng = Range("p2:p50000")
For ix = Rng.Count To 1 Step -1
If Trim(Application.Substitute(Rng.Item(ix).Text, _
Chr(160), Chr(32))) = "" Then
Rng.Item(ix).EntireRow.Delete
End If
Next
done:
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Object variable error 2
Inconsistency in Macro Speed 6
Replace Function in Excel 97 6
Macro to delete rows with text cells 8
Inefficient Space Removal 5
Summary based on field indentifier? 2
Fails to delete empty rows 4
change font of certain cells in row(s) when ColorIndex of Column S changes 4

Top