CODEWITHSUNDEEP
vbaexcel
Kyrbi
Kyrbi

Reputation: 2250

Deleting every second row in excel via VBA

I'd like to write a code, that checks how much rows there are in one sheet, then deletes every second row. The procedure should be like this:

  1. Get total number of rows
  2. Start with the last row, delete it
  3. Go up by two rows, delete it
  4. After you reach row number 2, stop

Here is my code which stops working at 8th row activeRow = activeRow - 2.

Sub trendlucid()
    Dim numOfRows As Integer
    Dim activeRow As Integer
    numOfRows = Sheets(actualsheet).UsedRange.Rows.Count
    activeRow = numOfRows
    Do While activeRow > 1
        Rows(activeRow).EntireRow.Delete
        activeRow = activeRow - 2
    Loop
End Sub

I have no idea why does it stop. Can anyone help me please?

Thank you and have a nice day

Upvotes: 1

Views: 1638

Answers (3)

Vityata
Vityata

Reputation: 43585

When deleting rows, loop back:

Sub TestMe()

    Dim numOfRows As Long
    Dim activeRow As Long
    numOfRows = ActiveSheet.UsedRange.Rows.Count
    activeRow = numOfRows

    Dim i As Long

    For i = numOfRows To 1 Step -2
        ActiveSheet.Rows(i).EntireRow.Delete
    Next i

End Sub

There is a bit faster way to delete rows, if you first save them in one range and at the end you delete the range. Thus, you use the delete operation only once:

https://stackoverflow.com/a/50350507/5448626

Upvotes: 2

DisplayName
DisplayName

Reputation: 13386

There are two flaws in your code

  1. unsure sheet reference

    you're counting number of UsedRange rows of Sheets(actualsheet) while you're deleting ActiveSheet rows (implicitly referenced sheet in Rows(activeRow).EntireRow.Delete)

    so be sure to reference the same sheet (e.g.: ActiveSheet)

  2. unsure range reference

    UsedRange may start form a row different than row 1, while Rows(index) consider a row index starting from row 1

    So reference UsedRange Rows() instead of ActiveSheet (implicitly assumed) Rows()

all this sums up to:

Sub trendlucid()
    Dim numOfRows As Long ' use Long instead of Integer to avoid overflow issue for more thna some 32k rows or so
    Dim activeRow As Integer
    With ActiveSheet.UsedRange ' reference "active" sheet 'UsedRange' range
        numOfRows = .Rows.Count ' count the number of rows of referenced range
        activeRow = numOfRows
        Do While activeRow > 1
            .Rows(activeRow).EntireRow.Delete ' reference 'UsedRange' 'Rows()' instead of 'ActiveSheet' 'Rows()'
            activeRow = activeRow - 2
        Loop
    End With
End Sub

Upvotes: 1

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

You must try something similar...

You don't need to use EntireRow with Rows, only Row Index is required and the Delete action will be performed to whole row.

Sub trendlucid()
    Dim numOfRows As Long
    Dim i As Long
    numOfRows = ActiveSheet.UsedRange.Rows.Count
    For i = numOfRows To 2 Step -2
        Rows(i).Delete
    Next i
End Sub

Upvotes: 0

Related Questions