Delete rows with VBA

You find different examples on this page to loop backwards through the rows and check If a specific value exists in a column (A is the column in my example) and delete that row if it exists. There are more ways to delete rows, you can also use Autofilter or Find to delete rows, see the link on the bottom of this page if you want to see some code how to do this.

The example macro loop through all the rows in the UsedRange. This is the first row with data till the last row with data on your worksheet. It can be slower if the column that you check doesn’t use so many rows or if your UsedRange is bigger than your data. You can find more information on Debra Dalgleish's website about this. http://www.contextures.com/xlfaqApp.html#Unused

You can replace this part of the macro
Firstrow = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

With this if you want to fill in the Firstrow and the Lastrow yourself.
Firstrow = 4
Lastrow = 100

Or with this if you want to fill in the Firstrow and let the code find the Lastrow in the column.
Firstrow = 2
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row

Note: you can also use a Function to find the last row with data, see this page : Find Last Row, Column or Cell

Macro Example

The code example below will delete every row in the usedrange with "ron" in the A column (case sensitive).
If .Value = "ron" Then .EntireRow.Delete

I use the A column in my example, change the A to your column in this code line.
With .Cells(Lrow, "A")

Note: Read the tips below the macro for many other examples.

Sub Loop_Example()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'We use the ActiveSheet but you can replace this with
'Sheets("MySheet")if you want
With ActiveSheet

'We select the sheet so we can change the window view
.Select

'If you are in Page Break Preview Or Page Layout view go
'back to normal view, we do this for speed
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

'Turn off Page Breaks, we do this for speed
.DisplayPageBreaks = False

'Set the first and last row to loop through
Firstrow = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

'We loop from Lastrow to Firstrow (bottom to top)
For Lrow = Lastrow To Firstrow Step -1

'We check the values in the A column in this example
With .Cells(Lrow, "A")

If Not IsError(.Value) Then

If .Value = "ron" Then .EntireRow.Delete
'This will delete each row with the Value "ron"
'in Column A, case sensitive.

End If

End With

Next Lrow

End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

Examples to change the example above

The example macro above will delete rows with "ron" in the A column now.

If .Value = "ron" Then .EntireRow.Delete
'This will delete each row with the Value "ron" in Column A, case sensitive.

You can replace the two lines above with one of the examples below. Note: I use text examples like ="ron" but you can also use = 1200 for numeric columns or use >, <, >=, <=. Tip: If you want to delete all rows that not have "ron" in column A then replace = for <>

If .Value Like "*ron*" Then .EntireRow.Delete
'This will delete each row where "ron" is a part of the string, case sensitive.
' Use "ron*" for a value that start with ron or "*ron" for a value that ends with ron

If LCase(.Value) = LCase("ron") Then .EntireRow.Delete
'This will delete each row with the Value "ron" in Column A, not case sensitive.

If Trim(.Value) = "ron" Then .EntireRow.Delete
'It uses the Trim function to strip the spaces
'This will delete each row with the Value "ron" in Column A, case sensitive.

If Trim(LCase(.Value)) = LCase("ron") Then .EntireRow.Delete
'It uses the Trim function to strip the spaces
'This will delete each row with the Value "ron" in Column A, not case sensitive.

If .Value = "" Then .EntireRow.Delete
'This will delete each row if the cell is empty or have a formula that evaluates to ""

If IsEmpty(.Value) Then .EntireRow.Delete
'This will delete the row if the cell is empty

Select Case .Value
Case Is = "jelle", "ron", "dave": .EntireRow.Delete
End Select

'Use Select Case if you want to check more values in the cell

If Not IsError(Application.Match(.Value, _
Array("jelle", "ron", "dave"), 0)) Then .EntireRow.Delete
'Or use this one with Application.Match if you want to check more values in the cell. You can also use a 'range with the values to delete. Replace Array("jelle", "ron", "dave") with Sheets("Sheet1").Range("A1:A200")

Tip: To make it easier to edit you can define a variable to hold that array
ArrNames = Array("jelle", "ron", "dave")
If Not IsError(Application.Match(.Value, ArrNames, 0)) Then .EntireRow.Delete


Do not forget to add this dim line on top of the macro if you use this Dim ArrNames As Variant

Examples to Check a whole row or more columns

Replace this part of the macro with one of the code examples below

'We check the values in the A column in this example
With .Cells(Lrow, "A")

If Not IsError(.Value) Then

If .Value = "ron" Then .EntireRow.Delete
'This will delete each row with the Value "ron"
'in Column A, case sensitive.

End If

End With
Replace the code above with the code below

If .Cells(Lrow, "A").Value = "ron" And _
.Cells(Lrow, "B").Value = "dave" And _
.Cells(Lrow, "C").Value > 10 Then .Rows(Lrow).Delete

'Use this if you want to check for values in other columns also.

If Application.CountA(.Rows(Lrow)) = 0 Then .Rows(Lrow).Delete
'This will delete the row if the whole row is empty (all columns)

If Application.CountA(.Range(.Cells(Lrow, "A"), .Cells(Lrow, "E"))) = 0 Then .Rows(Lrow).Delete
'This will delete the row if the first 5 cells in the row are empty

If Application.CountA(.Cells(Lrow, 1).Range("A1,M1,X1")) = 0 Then .Rows(Lrow).Delete
'This will delete the row if the cells in A, M and X in the row are empty

If Application.CountIf(.Rows(Lrow), "ron") = 0 Then .Rows(Lrow).Delete
' Delete each row if the value "ron" not exist in the row (It will look in the whole row)

If Application.CountIf(.Rows(Lrow), "ron") > 0 Then .Rows(Lrow).Delete
' Delete each row if the value "ron" exist in the row (It will look in the whole row)

Tip: With CountIf you can use wild cards like this "*ron*" (is also working then if "ron" is a part of a cell)
Use "*ron" for a value that start with ron or "ron*" for a value that ends with ron. See the Excel help for more information about CountIf, You can also use the wildcard ?.

Delete Rows with Union (faster with a lot of rows)

The Macro below looks the same as the macro above but the differents is that this one collect first all the rows that it must delete with Union and then delete them in one step. If you run code on a lot of rows this can be faster.

Sub Union_Example()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim rng As Range

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'We use the ActiveSheet but you can replace this with
'Sheets("MySheet")if you want
With ActiveSheet

'We select the sheet so we can change the window view
.Select

'If you are in Page Break Preview Or Page Layout view go
'back to normal view, we do this for speed
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

'Turn off Page Breaks, we do this for speed
.DisplayPageBreaks = False

'Set the first and last row to loop through
Firstrow = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

'We loop from Lastrow to Firstrow (bottom to top)
For Lrow = Lastrow To Firstrow Step -1

'We check the values in the A column in this example
With .Cells(Lrow, "A")

If Not IsError(.Value) Then

If .Value = "ron" Then
'This will delete each row with the Value "ron"
'in Column A, case sensitive.

If rng Is Nothing Then
Set rng = .Cells
Else
Set rng = Application.Union(rng, .Cells)
End If
End If

End If
End With

Next Lrow

End With

'Delete all rows in one time
If Not rng Is Nothing Then rng.EntireRow.Delete

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

Delete Rows with AutoFilter or Find Examples

I add a few examples for Autofilter and Find in the txt file below that will open in your browser: MacMoreDeleteCode.txt
31-July-2021
Web design by Will Woodgate