Macro to Delete Rows in Excel

These VBA macros delete the selected rows, a number of rows from the active cell, or every other row in a list.

Works inExcel 2010 and later, Windows and Mac
UndoNot possible. Save a copy first
Save asMacro-Enabled Workbook (.xlsm)

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

Delete the selected rows

Sub DeleteSelectedRows()
    Selection.EntireRow.Delete
End Sub

Works with several separate selections too: hold Ctrl while selecting.

Delete a number of rows from the active cell

Sub DeleteRowsFromActive()
    Dim n As Long
    n = Application.InputBox("How many rows to delete?", Type:=1)
    If n < 1 Then Exit Sub
    ActiveCell.EntireRow.Resize(n).Delete
End Sub

Delete every other row

This keeps the header in row 1 and rows 2, 4, 6 and so on, and deletes rows 3, 5, 7 and so on:

Sub DeleteEveryOtherRow()
    Dim i As Long, last As Long
    last = Cells(Rows.Count, "A").End(xlUp).Row
    For i = last To 2 Step -1
        If i Mod 2 = 1 Then Rows(i).Delete
    Next i
End Sub

last finds the last used row in column A. The loop runs bottom-up so deleting a row doesn’t shift the ones still to check. Change = 1 to = 0 to delete the even rows instead.

Things to know

  • There’s no Undo. Save a copy first.
  • Formulas that referred to a deleted row show #REF!.
  • Deleting rows inside an Excel Table removes them from the table.

Related macros