# Delete or Insert Rows Based on Cell Value

Written by

Reviewed by

In this Article

This tutorial will demonstrate how to delete or insert rows based on cell values.

## Delete Row Based on Cell Value

This will loop through a range, and delete rows if column A says “delete”.

```
Sub DeleteRowsBasedonCellValue()
'Declare Variables
Dim LastRow As Long, FirstRow As Long
Dim Row As Long
With ActiveSheet
'Define First and Last Rows
FirstRow = 1
LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
'Loop Through Rows (Bottom to Top)
For Row = LastRow To FirstRow Step -1
If .Range("A" & Row).Value = "delete" Then
.Range("A" & Row).EntireRow.Delete
End If
Next Row
End With
End Sub
```

We must start the loop with the bottom row because deleting a row will shift the data, skipping rows if you loop top to bottom.

Also, notice that instead of manually entering in the last row, we calculate the last used row.

### Delete Row – Based on Filter

In the previous example, we looped through the rows, deleting each row that meets the criteria. Alternatively, we can use Excel’s AutoFilter to filter rows based on some criteria and then delete the visible rows:

```
Sub FilterAndDeleteRows()
'Declare ws variable
Dim ws As Worksheet
Set ws = ActiveSheet
'Reset Existing Filters
On Error Resume Next
ws.ShowAllData
On Error GoTo 0
'Apply Filter
ws.Range("a1:d100").AutoFilter Field:=1, Criteria1:="delete"
'Delete Rows
Application.DisplayAlerts = False
ws.Range("a1:d100").SpecialCells(xlCellTypeVisible).Delete
Application.DisplayAlerts = True
'Clear Filter
On Error Resume Next
ws.ShowAllData
On Error GoTo 0
End Sub
```

### Delete Row Based on Cell Criteria

This will loop through a range, deleting rows if the cell in column A meets certain criteria (< 0):

```
Sub DeleteRowsBasedonCellValue()
'Declare Variables
Dim LastRow As Long, FirstRow As Long
Dim Row As Long
With ActiveSheet
'Define First and Last Rows
FirstRow = 1
LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
'Loop Through Rows (Bottom to Top)
For Row = LastRow To FirstRow Step -1
If .Range("A" & Row).Value < 0 Then
.Range("A" & Row).EntireRow.Delete
End If
Next Row
End With
End Sub
```

### Delete Row if Cell is Blank

This will loop through a range, deleting a row if a cell in column A is blank:

```
Sub DeleteRowsBasedonCellValue()
'Declare Variables
Dim LastRow As Long, FirstRow As Long
Dim Row As Long
With ActiveSheet
'Define First and Last Rows
FirstRow = 1
LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
'Loop Through Rows (Bottom to Top)
For Row = LastRow To FirstRow Step -1
If .Range("A" & Row).Value = "" Then
.Range("A" & Row).EntireRow.Delete
End If
Next Row
End With
End Sub
```

### Delete Blank Row

Alternatively, if you want to delete a row if the entire row is blank (Click link for a slightly different method), you can use this code:

```
Sub DeleteBlankRows()
'Declare Variables
Dim LastRow As Long, FirstRow As Long
Dim Row As Long
With ActiveSheet
'Define First and Last Rows
FirstRow = 1
LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
'Loop Through Rows (Bottom to Top)
For Row = LastRow To FirstRow Step -1
If WorksheetFunction.CountA(.Rows(Row)) = 0 Then
.Rows(Row).EntireRow.Delete
End If
Next Row
End With
End Sub
```

### Delete Row if Cell Contains Value

This will loop through a range, deleting a row if the cell in column A is not blank:

```
Sub DeleteRowsBasedonCellValue()
'Declare Variables
Dim LastRow As Long, FirstRow As Long
Dim Row As Long
With ActiveSheet
'Define First and Last Rows
FirstRow = 1
LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
'Loop Through Rows (Bottom to Top)
For Row = LastRow To FirstRow Step -1
If .Range("A" & Row).Value <>"" Then
.Range("A" & Row).EntireRow.Delete
End If
Next Row
End With
End Sub
```

## Insert Row Based on Cell Value

This will loop through a range, inserting rows if a certain cell in that row says “insert”:

```
Sub InsertRowsBasedonCellValue()
'Declare Variables
Dim LastRow As Long, FirstRow As Long
Dim Row As Long
With ActiveSheet
'Define First and Last Rows
FirstRow = 1
LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
'Loop Through Rows (Bottom to Top)
For Row = LastRow To FirstRow Step -1
If .Range("A" & Row).Value = "insert" Then
.Range("A" & Row).EntireRow.Insert
End If
Next Row
End With
End Sub
```