Slow Delete Blank Rows Macro
 

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

Slow Delete Blank Rows Macro
 

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

Slow Delete Blank Rows Macro
 

What about

Range("P2:P50000").SpecialCells(xlCellTypeBlanks). EntireRow.Delete

?

HTH,
JP

On Mar 29, 8:40*pm, Monk wrote:
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

Slow Delete Blank Rows Macro
 

Thanks Jim

"Jim Cone" wrote:

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

Slow Delete Blank Rows Macro
 

Warning

See this page
http://www.rondebruin.nl/delete.htm#Specialcells

--

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


"JP" wrote in message ...
What about

Range("P2:P50000").SpecialCells(xlCellTypeBlanks). EntireRow.Delete

?

HTH,
JP

On Mar 29, 8:40 pm, Monk wrote:
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

Slow Delete Blank Rows Macro
 

Had no idea, thanks Ron!

--JP

On Mar 30, 4:31*pm, "Ron de Bruin" wrote:
Warning

See this pagehttp://www.rondebruin.nl/delete.htm#Specialcells

--