VBA to delete every other line

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: VBA to delete every other line

  1. #1
    Board Regular
    Join Date
    Mar 2005
    Location
    London, England
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to delete every other line

     
    Hello,

    If somebody has some VBA code written to delete every other line from row x to row y, it would be much appreciated! I'm very new to VBA and don't know how to go about doing this.

    Thanks very much,

    Tim

  2. #2
    Board Regular
    Join Date
    Apr 2004
    Posts
    7,770
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dim c As Range, rng
    Set rng = Range("a11:a35")
    For Each c In rng
    If c.Row() / 2 > 0 Then
    c.EntireRow.Delete
    End If
    Next c
    Yesterday I felt on top of the world. Today its falling in on me.

  3. #3
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,240
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Delete alternate rows

    Hi Tim,

    Here is one that prompts you for the range of rows.

    Sub DelAlternateRows()
    'Deletes every other row in a defined range of rows
    'Examples: start row = 10, end row = 20, deletes rows 11, 13, 15, 17, 19
    ' start row = 1, end row = 10, deletes rows 2, 4, 6, 8, 10
    ' start row = 0, end row = 10, deletes rows 1, 3, 5, 7, 9
    ' Prompts for start and end rows.

    Dim Rows2Delete As Range
    Dim StartRow As Long
    Dim EndRow As Long
    Dim iRow As Long

    StartRow = Application.InputBox("Enter Start Row", "Delete Alternate Rows", _
    1, Type:=1)
    With ActiveSheet.UsedRange
    EndRow = .Row + .Rows.Count - 1
    End With
    EndRow = Application.InputBox("Enter End Row", "Delete Alternate Rows", _
    EndRow, Type:=1)
    iRow = StartRow + 1
    Set Rows2Delete = Rows(iRow)
    iRow = iRow + 2

    Do While iRow <= EndRow
    Set Rows2Delete = Union(Rows2Delete, Rows(iRow))
    iRow = iRow + 2
    Loop

    Rows2Delete.Delete

    End Sub


    Damon

  4. #4
    Board Regular
    Join Date
    Mar 2005
    Location
    London, England
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks very much to both of you - that's really helpful.

    Tim

  5. #5
    New Member
    Join Date
    Jun 2009
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to delete every other line

      
    Damon,
    how do i modify your macro to change it to delete every 2 row ? Do i need to input another set of commands i.e. range and select range ?

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com