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