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.

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
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.

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
05/09/2024
Web design by Will Woodgate