Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete every other line in LARGE worksheet
Can anyone think of a FAST way to delete every other line in a 45,000
line worksheet? I can do a loop with Range("A"&Loop).EntireRow.Delete, but it takes almost 10 minutes. I was thinking of somehow selecting every other line and doing one HUGE delete, but not sure if Excel could handle that or if it would be any faster. Any ideas?!! Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete every other line in LARGE worksheet
You could use a helper column with
=ISEVEN(ROW()) and copy down. Then copy/paste special the values to hardcode the TRUE/FALSE values, sort the list (using this new column) and delete the rows you need. But I have sometimes had problems w/deleting large ranges, sometimes even having the computer lock up on me. This macro took about 3 minutes to delete rows 1 through 45001 (every other row - change as needed). I set it to delete about 2000 rows at a time as it seems VBA started slowing down as the range to delete gets bigger. It seemed to me faster than deleting one large range at the end and deleting one row at a time. You can, of course change it as you see fit. Sub Delete() Dim i As Long Dim rngDelete As Range Application.ScreenUpdating = False With Sheet1 For i = 45001 To 1 Step -2 If rngDelete Is Nothing Then Set rngDelete = .Cells(i, 1) Else Set rngDelete = Union(rngDelete, _ .Cells(i, 1)) End If Application.StatusBar = i If rngDelete.Cells.Count Mod 2000 = 0 Then rngDelete.EntireRow.Delete Set rngDelete = Nothing End If Next i End With If Not rngDelete Is Nothing Then _ rngDelete.EntireRow.Delete Application.ScreenUpdating = True Application.StatusBar = False End Sub " wrote: Can anyone think of a FAST way to delete every other line in a 45,000 line worksheet? I can do a loop with Range("A"&Loop).EntireRow.Delete, but it takes almost 10 minutes. I was thinking of somehow selecting every other line and doing one HUGE delete, but not sure if Excel could handle that or if it would be any faster. Any ideas?!! Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete every other line in LARGE worksheet
Make sure you back your data up before trying new things.
"JMB" wrote: You could use a helper column with =ISEVEN(ROW()) and copy down. Then copy/paste special the values to hardcode the TRUE/FALSE values, sort the list (using this new column) and delete the rows you need. But I have sometimes had problems w/deleting large ranges, sometimes even having the computer lock up on me. This macro took about 3 minutes to delete rows 1 through 45001 (every other row - change as needed). I set it to delete about 2000 rows at a time as it seems VBA started slowing down as the range to delete gets bigger. It seemed to me faster than deleting one large range at the end and deleting one row at a time. You can, of course change it as you see fit. Sub Delete() Dim i As Long Dim rngDelete As Range Application.ScreenUpdating = False With Sheet1 For i = 45001 To 1 Step -2 If rngDelete Is Nothing Then Set rngDelete = .Cells(i, 1) Else Set rngDelete = Union(rngDelete, _ .Cells(i, 1)) End If Application.StatusBar = i If rngDelete.Cells.Count Mod 2000 = 0 Then rngDelete.EntireRow.Delete Set rngDelete = Nothing End If Next i End With If Not rngDelete Is Nothing Then _ rngDelete.EntireRow.Delete Application.ScreenUpdating = True Application.StatusBar = False End Sub " wrote: Can anyone think of a FAST way to delete every other line in a 45,000 line worksheet? I can do a loop with Range("A"&Loop).EntireRow.Delete, but it takes almost 10 minutes. I was thinking of somehow selecting every other line and doing one HUGE delete, but not sure if Excel could handle that or if it would be any faster. Any ideas?!! Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete every other line in LARGE worksheet
This took about 4 seconds...
Sub AAA() Dim N As Long Dim rng As Range Set rng = Range("A1", "A45000") For N = 45000 To 2 Step -2 rng(N).EntireRow.ClearContents Next rng.EntireRow.Sort Range("A1") End Sub -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware wrote in message oups.com... Can anyone think of a FAST way to delete every other line in a 45,000 line worksheet? I can do a loop with Range("A"&Loop).EntireRow.Delete, but it takes almost 10 minutes. I was thinking of somehow selecting every other line and doing one HUGE delete, but not sure if Excel could handle that or if it would be any faster. Any ideas?!! Thanks. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete every other line in LARGE worksheet
Not sure how much it will help but if you wrap the entire thing in
Application.ScreenUpdating = False and Application.ScreenUpdating = True when done it may help speed things up as Windows won't be wasting time blinking the screen on you... "Jim Cone" wrote in message ... This took about 4 seconds... Sub AAA() Dim N As Long Dim rng As Range Set rng = Range("A1", "A45000") For N = 45000 To 2 Step -2 rng(N).EntireRow.ClearContents Next rng.EntireRow.Sort Range("A1") End Sub -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware wrote in message oups.com... Can anyone think of a FAST way to delete every other line in a 45,000 line worksheet? I can do a loop with Range("A"&Loop).EntireRow.Delete, but it takes almost 10 minutes. I was thinking of somehow selecting every other line and doing one HUGE delete, but not sure if Excel could handle that or if it would be any faster. Any ideas?!! Thanks. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete every other line in LARGE worksheet
Thanks for all the great ideas!
Yes, already have the screen updating off. That does make a big difference. The clear every other row and re-sort works great. Another fast solution I found is to simply parse every other row into a new spreadsheet. The secret seems to not use the DELETE command but to find some other way of getting the data I want out of the larger set. Thanks again for all the ideas! Dove wrote: Not sure how much it will help but if you wrap the entire thing in Application.ScreenUpdating = False and Application.ScreenUpdating = True when done it may help speed things up as Windows won't be wasting time blinking the screen on you... "Jim Cone" wrote in message ... This took about 4 seconds... Sub AAA() Dim N As Long Dim rng As Range Set rng = Range("A1", "A45000") For N = 45000 To 2 Step -2 rng(N).EntireRow.ClearContents Next rng.EntireRow.Sort Range("A1") End Sub -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware wrote in message oups.com... Can anyone think of a FAST way to delete every other line in a 45,000 line worksheet? I can do a loop with Range("A"&Loop).EntireRow.Delete, but it takes almost 10 minutes. I was thinking of somehow selecting every other line and doing one HUGE delete, but not sure if Excel could handle that or if it would be any faster. Any ideas?!! Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Challenge - Excel Line Feed Character CHR(10) - How to Delete and keep the text formatting without going ro single line in a cell ? | Excel Worksheet Functions | |||
delete duplicate entries in a large worksheet | Excel Discussion (Misc queries) | |||
How do I get to the last line of a large spreadsheet to add data | Excel Worksheet Functions | |||
Using SpecialCells().EntireRow.Delete on large worksheet - Very slow! | Excel Programming | |||
Attaching a JET database to an Excel Worksheet OR storing large binary data in a worksheet | Excel Programming |