VBA Delete Entire Row or Column

In this Article

Delete Entire Row or Column
- Delete Multiple Rows or Columns
Delete Blank / Empty Rows
- Delete Row if Cell is Blank
Delete Row Based on Cell Value
More Delete Row and Column Examples

This tutorial will demonstrate different ways to delete rows and columns in Excel using VBA.

Delete Entire Row or Column

To delete an entire row in VBA use this line of code:

Rows(1).Delete

Notice we use the Delete method to delete a row.

Instead of referencing the Rows Object, you can reference rows based on their Range Object with EntireRow:

Range("a1").EntireRow.Delete

Similarly to delete an entire column, use these lines of code:

Columns(1).Delete

Range("a1").EntireColumn.Delete

Delete Multiple Rows or Columns

Using the same logic, you can also delete multiple rows at once:

Rows("1:3").Delete

or columns:

Columns("A:C").Delete

Notice here we reference the specific row and column numbers / letters surrounded by quotations.

Of course, you can also reference the EntireRow of a range:

Range("a1:a10").EntireRow.Delete

Note: The examples below only demonstrate deleting rows, however as you can see above, the syntax is virtually identically to delete columns.

Delete Blank / Empty Rows

This example will delete a row if the entire row is blank:

Sub DeleteRows_EntireRowBlank()

Dim cell As Range

For Each cell In Range("b2:b20")
    If Application.WorksheetFunction.CountA(cell.EntireRow) = 0 Then
        cell.EntireRow.Delete
    End If
Next cell

End Sub

It makes use of the Excel worksheet function: COUNTA.

Delete Row if Cell is Blank

This will delete a row if specific column in that row is blank (in this case column B):

Range("b3:b20").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Delete Row Based on Cell Value

This will loop through a range, and delete rows if a certain cell value in that row says “delete”.

Sub DeleteRowswithSpecificValue()

Dim cell As Range

For Each cell In Range("b2:b20")
    If cell.Value = "delete" Then
        cell.EntireRow.Delete
    End If
Next cell

End Sub

More Delete Row and Column Examples

Delete Duplicate Rows

This code will delete all duplicate rows in a range:

Range("b2:c100").RemoveDuplicates Columns:=2

Notice we set Columns:=2. This tells VBA to check both the first two columns of data when considering if rows are duplicates. A duplicate is only found when both columns have duplicate values.

If we had set this to 1, only the first row would’ve been checked for duplicate values.

Delete Table Rows

This code will delete the second row in a Table by referencing ListObjects.

ThisWorkbook.Sheets("Sheet1").ListObjects("list1").ListRows(2).Delete

Delete Filtered Rows

To delete only rows that are visible after filtering:

Range("b3:b20").SpecialCells(xlCellTypeVisible).EntireRow.Delete

Delete Rows in Range

This code will delete all rows in range:

Range("a1:a10").EntireRow.Delete

Delete Selected Rows

This code will delete all selected rows:

Selection.EntireRow.Delete

Delete Last Row

This will delete the last used row in column B:

Cells(Rows.Count, 2).End(xlUp).EntireRow.Delete

By changing 2 to 1, you can delete the last used row in column A, etc.:

Cells(Rows.Count, 1).End(xlUp).EntireRow.Delete

Delete Columns by Number

To delete a column by it’s number, use a code like this:

Columns (2).Delete