UtterAccess Discussion Forums > Delete every other line macro

Full Version: Delete every other line macro

UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel

rminche

Aug 10 2006, 12:06 PM

I was wondering if there is a way to get a macro to delete every other line in a spreadsheet?

Luceze

Aug 10 2006, 12:31 PM

In the entire worksheet?

This will do it for the used range. Notice I commented out the delete line.

CODE

[color="blue"]Sub[/color] DeleteRows()
    [color="blue"]Dim[/color] rng1 [color="blue"]As[/color] Range
    [color="blue"]Dim[/color] i [color="blue"]As[/color] [color="blue"]Long[/color]
        [color="blue"]For[/color] i = 1 [color="blue"]To[/color] ActiveSheet.UsedRange.Rows.Count [color="blue"]Step[/color] 2
            [color="blue"]If[/color] rng1 [color="blue"]Is[/color] [color="blue"]Nothing[/color] [color="blue"]Then[/color]
                [color="blue"]Set[/color] rng1 = Cells(i, 1)
                    [color="blue"]Else[/color]
                [color="blue"]Set[/color] rng1 = Union(rng1, Cells(i, 1))
            [color="blue"]End[/color] [color="blue"]If[/color]
        [color="blue"]Next[/color]
    rng1.EntireRow.Select
    [color="green"]'rng1.EntireRow.delete[/color]
    [color="blue"]Set[/color] rng1 = [color="blue"]Nothing[/color]
[color="blue"]End[/color] [color="blue"]Sub[/color]

HTH,

rminche

Aug 10 2006, 02:19 PM

thanks!!!!!!!that works great.

Can it be changed from every third or fourth with this code also??

rminche

Aug 10 2006, 02:25 PM

I figured it out thanks so much for your help. You are awesome!!!!!

rminche

Aug 10 2006, 03:03 PM

Is there a dummy book I can read about this stuff?

Also, can you select the third & fourth line at the same time to delete and set a range like from 1 to 100?

Luceze

Aug 10 2006, 03:49 PM

Like this?

CODE

Sub DeleteRows()
Application.ScreenUpdating = False
    Dim rng1 As Range
    Dim i As Long, n As Long
        For i = 1 To 100
        n = n + 1
            If n = 3 Or n = 4 Then
                If rng1 Is Nothing Then
                    Set rng1 = Cells(i, 1)
                        Else
                    Set rng1 = Union(rng1, Cells(i, 1))
                End If
                If n = 4 Then n = 0
            End If
        Next
    rng1.EntireRow.Select
    'rng1.EntireRow.delete
    Set rng1 = Nothing
End Sub

rminche

Aug 11 2006, 09:22 AM

wonderful!!!!!!!!!

any suggestions on a book that helps you learn this?

Luceze

Aug 11 2006, 10:27 AM

Try here.

Nate and Martin reference some good reading materials.

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.