Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete blank rows Macro | Excel Discussion (Misc queries) | |||
Using a macro to delete all blank rows | Excel Programming | |||
Macro to delete blank rows | Excel Programming | |||
Macro to delete blank rows | Excel Programming | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming |