CODEWITHSUNDEEP
excelvba
rvphx
rvphx

Reputation: 2402

Excel 2010 delete every 57th (and 4 more) rows

I need a macro code to delete every 58th row and 4 more rows after the 57th row. i.e I need to delete row numbers 58,59,60,61 and 62nd (5 total).

I need to repeat this pattern on the entire sheet. So in effect after the macro deletes the 58,59,60,61 and 62nd rows, it needs to delete 115,116,117,118 and 119th row (difference of 57 between them 115-57 = 58).

I tried looking up on the internet, but could not find an article matching my needs. Any help will be appreciated.

Thanks.

Upvotes: 0

Views: 4435

Answers (2)

lori_m
lori_m

Reputation: 5577

For a non-code method you could choose a blank column beside your data and try these steps:

  • Select the range e.g. A57:A60 and enter X with ctrl+enter to fill the cells
  • Select the repeating range e.g. A1:A60, and fill down to the end of the data (or use copy/paste)
  • Press Ctrl+Shift+\ to select all the X's (goto special... column differences or use autofilter)
  • Choose Delete > Sheet Rows from the Cells section of the Home Tab

You're done. This also preserves the undo stack so you can backtrack if required.

Upvotes: 4

assylias
assylias

Reputation: 328923

Next time you post a question, you would get better attention and feedback if at least you tried something, anything really. A good first step is to run the macro recorder in Excel, delete a few rows and check the code that has been generated. From there, the final solution is not very far.

Sub deleteRows()

    Dim i As Long
    Dim maxRow As Long

    maxRow = ActiveSheet.UsedRange.Rows.Count

    For i = 58 To maxRow Step 52 'Step 52, not 57, because we delete 5 rows each time
        Range(Rows(i), Rows(i + 4)).Delete Shift:=xlUp
    Next i

End Sub

Upvotes: 2

Related Questions