Results 1 to 5 of 5

VBA to delete every other line

This is a discussion on VBA to delete every other line within the Excel Questions forums, part of the Question Forums category; Hello, If somebody has some VBA code written to delete every other line from row x to row y, it ...

  1. #1
    Board Regular
    Join Date
    Mar 2005
    Location
    London, England
    Posts
    54

    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
    6,986

    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,220

    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

    Default

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

    Tim

  5. #5
    New Member
    Join Date
    Jun 2009
    Posts
    1

    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 ?

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