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.

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


"[email protected]" 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.
>
>

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
>
>
> "[email protected]" 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.
> >
> >

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


<[email protected]>
wrote in message
news:[email protected]...
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.

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
> <[email protected]>
> wrote in message
> news:[email protected]...
> 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.
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
> > <[email protected]>
> > wrote in message
> > news:[email protected]...
> > 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.
> >