How to Delete Row or Column in Excel VBA ?

In this post, you’ll learn how you can delete row or column from your excel spreadsheet programmatically using Excel VBA.

Delete Entire Row or Column in Excel VBA

To delete an entire row in VBA, use the below code snippet

Code:

Rows(1).Delete

or

Code:

Range("a1").EntireRow.Delete

To delete an entire column in VBA

Code:

Columns(1).Delete
Range("a1").EntireColumn.Delete

Delete Multiple Rows or Columns

To delete multiple rows at once,

Code:

Rows("1:3").Delete

For columns,

Code:

Columns("A:C").Delete

Delete Blank / Empty Rows

To delete blank or empty Rows,

Code:

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

Delete Blank Row

To delete a Blank Row

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

Delete Row Based on Cell Value

To delete row based on cell value,

Code:

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

Delete Duplicate Rows

To delete all duplicate rows in a range,

Code:

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

Delete Table Rows

To delete the rows in a Table by referencing ListObjects,

Code:

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

Delete Filtered Rows

To delete only rows that is visible after filtering,

Code:

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

Delete Rows in Range

To delete all rows in range,

Code:

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

Delete Selected Rows

To delete all selected rows,

Code:

Selection.EntireRow.Delete

Delete Last Row

To delete last row,

Column

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

Delete Columns by Number

To delete a column by its number,

Code:

Columns (2).Delete