ExcelInsider Team
When working with Excel data, you may find rows that are not useful. These could be blank rows, duplicates, rows with errors, or entries that do not meet your specific needs. Deleting them one by one can take time and lead to mistakes.
In this article, we will show you how to use VBA macros to delete rows based on different criteria. Each method covers a specific case, such as removing rows that start with a certain character, contain keywords, or show errors. You can use these macros as they are or adjust them to fit your data.
Steps to delete rows in columns A–C that contain a user‑entered keyword:
➤ In Sheet1, type a keyword in cell E2.
➤ Press Alt + F11 , insert a new module, and paste the code below:
Sub DeleteRows_UserKeyword()
Dim ws As Worksheet
Dim keyword As String
Dim lastRow As Long
Dim i As Long, j As Long
Dim deleteRow As Boolean
Const FirstDataRow As Long = 2 ' header row is 1
Const LastDataCol As Long = 3 ' examine columns A–C only
Set ws = ThisWorkbook.Sheets("Sheet1")
keyword = Trim(ws.Range("E2").Value)
If keyword = "" Then
MsgBox "Please enter a keyword in cell E2.", vbExclamation
Exit Sub
End If
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
For i = lastRow To FirstDataRow Step -1 ' bottom to top
deleteRow = False
For j = 1 To LastDataCol ' columns A–C
If InStr(1, ws.Cells(i, j).Value, keyword, vbTextCompare) > 0 Then
deleteRow = True
Exit For
End If
Next j
If deleteRow Then ws.Rows(i).Delete
Next i
MsgBox "Rows in A–C containing '" & keyword & "' deleted.", vbInformation
End Sub➤ Run via Alt + F8 >> DeleteRows_UserKeyword > Run, and rows with the keyword inside the table disappear.
Delete Rows Containing Cells Starting with a Particular Character
You may want to remove rows where a cell in a specific column begins with a certain character. For example, in an interview results list, rows where the Status starts with “R” for Rejected can be deleted to keep only selected candidates.
For this example, our dataset has columns ID, Candidate Name, and Status on Sheet1. Some rows have Status marked as “Rejected.” The macro below deletes every row where the Status starts with the letter “R.”
Steps:
➤ Press Alt + F11 , choose Insert >> Module.
➤ Paste this code:
Sub DeleteRowsStartingWithChar()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim checkCol As Long
Dim checkChar As String
Set ws = ThisWorkbook.Sheets("Sheet1") ' Change sheet name if needed
checkCol = 3 ' Column C (Status)
checkChar = "R" ' Character to check (e.g., "R" for Rejected)
lastRow = ws.Cells(ws.Rows.Count, checkCol).End(xlUp).Row
' Loop from bottom to top to avoid skipping rows after deletion
For i = lastRow To 2 Step -1 ' Assuming row 1 has headers
If Left(ws.Cells(i, checkCol).Value, 1) = checkChar Then
ws.Rows(i).Delete
End If
Next i
MsgBox "Rows starting with '" & checkChar & "' in column " & ws.Cells(1, checkCol).Address(False, False) & " deleted.", vbInformation
End Sub 
➧ checkChar is the character the cell value must start with to trigger deletion.
➧ The macro loops from the last data row up to row 2, deleting rows where the cell in column C starts with “R.”
➧ The message box confirms the operation and shows which character was targeted.
➤ Go back to Excel, press Alt + F8 .
➤ Select DeleteRowsStartingWithChar and click Run.
➤ All rows with Status starting with “R” (Rejected) will be removed, leaving only candidates who are selected.
Remove Rows Containing a Specific Keyword in a Column
If you want to delete rows where a cell matches a specific keyword exactly, such as removing all candidates marked “Rejected,” VBA can quickly do this for you. This method targets rows based on an exact match rather than just starting characters.
For this example, we use the same dataset with columns ID, Candidate Name, and Status. The macro deletes every row where Status equals the keyword “Rejected.”
Steps:
➤ Press Alt + F11 , choose Insert >> Module.
➤ Paste this code:
Sub DeleteRowsWithKeyword()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim checkCol As Long
Dim keyword As String
Set ws = ThisWorkbook.Sheets("Sheet1") ' Change sheet name if needed
checkCol = 3 ' Column C (Status)
keyword = "Rejected" ' Exact keyword to delete rows
lastRow = ws.Cells(ws.Rows.Count, checkCol).End(xlUp).Row
' Loop from bottom to top to avoid skipping rows after deletion
For i = lastRow To 2 Step -1 ' Assuming row 1 has headers
If ws.Cells(i, checkCol).Value = keyword Then
ws.Rows(i).Delete
End If
Next i
MsgBox "Rows with '" & keyword & "' in column " & ws.Cells(1, checkCol).Address(False, False) & " deleted.", vbInformation
End Sub
➧ keyword defines the exact cell value to look for (“Rejected”).
➧ The macro loops upward through the rows, deleting those with a Status exactly equal to “Rejected.”
➧ A confirmation message appears after completion.
➤ Return to Excel, press Alt + F8 . Select DeleteRowsWithKeyword and click Run.
➤ All rows with Status “Rejected” will be deleted, leaving only the selected candidates.
Identify Rows Based on Multiple Criteria and Delete Them in VBA
Managing shipments means quickly removing records that don’t meet certain conditions. Sometimes you need to delete rows only when more than one condition is met. For example, you might want to remove rows where the shipment was cancelled and the item is a specific product. VBA lets you apply multiple criteria to delete only those rows matching all conditions.
In this example, our shipment dataset on Sheet1 has columns Order ID, Item Name, and Shipment Status. The macro deletes rows where the Shipment Status is “Order Cancelled” and the Item Name is “USB Cable.”
Steps:
➤ Press Alt + F11 , choose Insert >> Module, and paste this code:
Sub DeleteRows_MultipleCriteria()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim itemCol As Long, statusCol As Long
Set ws = ThisWorkbook.Sheets("Sheet1") ' change if needed
itemCol = 2 ' Item Name is column B
statusCol = 3 ' Shipment Status is column C
lastRow = ws.Cells(ws.Rows.Count, statusCol).End(xlUp).Row
' Loop from bottom to top to avoid skipping rows after deletion
For i = lastRow To 2 Step -1 ' assumes row 1 is header
If ws.Cells(i, statusCol).Value = "Order Cancelled" And _
ws.Cells(i, itemCol).Value = "USB Cable" Then
ws.Rows(i).Delete
End If
Next i
MsgBox "Rows where Item is 'USB Cable' and Status is 'Order Cancelled' deleted.", vbInformation
End Sub 
➧ It finds the last used row in column C to define the loop range.
➧ The loop runs from bottom to top to avoid skipping rows after deletion.
➧ Only rows where both conditions (Item Name = “USB Cable” AND Shipment Status = “Order Cancelled”) are met will be deleted.
➧ A message box confirms the operation is complete.
➤ Return to Excel, press Alt + F8 , select DeleteRows_MultipleCriteria, and click Run.
➤ Only rows that have “USB Cable” as the item and “Order Cancelled” as status will be removed.
Filter and Delete Rows Based on User Input Criteria
In some cases, you want to delete rows based on a keyword that you decide at runtime. Instead of hardcoding the criteria, this method lets you type a keyword in a specific cell, and the macro deletes every row where any cell in that row contains this keyword.
For this demonstration, we will use the same shipping information dataset. The keyword will be typed in the worksheet. It can be any word appearing anywhere in the data. The macro will scan all columns in each row and delete rows containing the keyword.
Steps:
➤ In Sheet1, type your keyword in cell E2.
➤ Press Alt + F11 , choose Insert >> Module, and paste this code:
Sub DeleteRows_UserKeyword()
Dim ws As Worksheet
Dim keyword As String
Dim lastRow As Long
Dim i As Long, j As Long
Dim deleteRow As Boolean
Const FirstDataRow As Long = 2 ' row 1 holds headers
Const LastDataCol As Long = 3 ' columns A‑C only
Set ws = ThisWorkbook.Sheets("Sheet1") ' adjust if needed
keyword = Trim(ws.Range("E2").Value)
If keyword = "" Then
MsgBox "Please enter a keyword in cell E2.", vbExclamation
Exit Sub
End If
' Determine last row based on column A (ID)
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
' Loop bottom‑to‑top through the data block (A:C)
For i = lastRow To FirstDataRow Step -1
deleteRow = False
For j = 1 To LastDataCol
If InStr(1, ws.Cells(i, j).Value, keyword, vbTextCompare) > 0 Then
deleteRow = True
Exit For
End If
Next j
If deleteRow Then ws.Rows(i).Delete
Next i
MsgBox "Rows in columns A‑C containing '" & keyword & "' deleted.", vbInformation
End Sub 
➧ lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row and lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column find the last used row and column in the worksheet, setting the data boundaries.
➧ The For r = lastRow To 2 Step -1 loop processes rows from bottom to top, preventing row skipping when rows are deleted.
➧ Inside the row loop, For c = 1 To lastCol iterates through each column in the current row to check all cells.
➧ The condition If InStr(1, ws.Cells(r, c).Value, keyword, vbTextCompare) > 0 tests if the keyword appears anywhere in the cell (case-insensitive).
➧ When a match is found, ws.Rows(r).Delete removes the entire row immediately, and the inner loop exits to move to the next row.
➧ After all rows are processed, MsgBox displays a confirmation message showing the keyword used and completion status.
➤ Return to Excel, enter your keyword in E2, press Alt + F8 , select DeleteRows_UserKeyword, and click Run.
➤ Rows containing that keyword anywhere in the data will be deleted.
Delete Rows Based on a Data Range Using VBA
When you need to remove student records whose scores fall inside a specific range, VBA can do the work automatically. By entering the minimum score in F1 and the maximum score in F2, the macro will delete every row whose Score value lies within that range.
For this demonstration, the dataset on Sheet1 contains columns ID, Student Name, and Score. The macro below clears rows whose Score is between the numbers you set in F1 and F2.
Steps:
➤ In your worksheet, type the minimum score in F1 and the maximum score in F2.
➤ Press Alt + F11 , choose Insert >> Module, and paste the code:
Sub DeleteRows_ScoreRange()
Dim ws As Worksheet
Dim minScore As Double, maxScore As Double
Dim lastRow As Long
Dim i As Long
Set ws = ThisWorkbook.Sheets("Sheet1") ' data sheet
Const ScoreCol As Long = 3 ' column C holds Score
' Get user‑defined limits from F1 (min) and F2 (max)
If IsNumeric(ws.Range("F1").Value) And IsNumeric(ws.Range("F2").Value) Then
minScore = CDbl(ws.Range("F1").Value)
maxScore = CDbl(ws.Range("F2").Value)
Else
MsgBox "Enter numeric values in F1 (min) and F2 (max).", vbExclamation
Exit Sub
End If
If minScore > maxScore Then
MsgBox "Minimum score cannot exceed maximum score.", vbExclamation
Exit Sub
End If
' Find last used row ONLY in the table area (column A–C)
lastRow = ws.Cells(ws.Rows.Count, ScoreCol).End(xlUp).Row
' Check scores in column C; ignore other columns for criteria
For i = lastRow To 2 Step -1 ' assumes headers in row 1
If IsNumeric(ws.Cells(i, ScoreCol).Value) Then
If ws.Cells(i, ScoreCol).Value >= minScore _
And ws.Cells(i, ScoreCol).Value <= maxScore Then
ws.Rows(i).Delete
End If
End If
Next i
MsgBox "Rows with scores between " & minScore & _
" and " & maxScore & " deleted.", vbInformation
End Sub 
➧ lastRow locates the bottom of the data in the Score column.
➧ The loop runs bottom to top so row indices stay correct after deletions.
➧ If ws.Cells(i, scoreCol).Value >= minScore And ... <= maxScore checks whether the score lies inside the user‑defined range; if true, that row is deleted.
➧ A message box confirms how many rows were removed and displays the range used.
➤ Return to Excel, ensure F1 and F2 contain your score limits, press Alt + F8 , select DeleteRows_ScoreRange, and click Run.
➤ All rows with scores within the specified range are removed, leaving only records outside that range.
Eliminate Duplicate Rows Based on a Single Column with VBA
Duplicate records can inflate file size and skew summaries. VBA can scan a column of your choice, keep the first occurrence of each value, and delete every subsequent duplicate row in seconds.
For this demonstration, Sheet1 holds an order log with the columns Order ID, Item, and Quantity. Several rows share the same Order ID, indicating duplicates that should be removed.
Steps:
➤ Press Alt + F11 , choose Insert >> Module, and paste the code:
Sub DeleteDuplicateRows_SpecificColumn()
Dim ws As Worksheet
Dim dataRange As Range
Dim dupCol As Long
Set ws = ThisWorkbook.Sheets("Sheet1") ' sheet to clean
dupCol = 1 ' column 1 = Order ID
' Define the block that contains data (including headers)
Set dataRange = ws.Range("A1").CurrentRegion
' Remove duplicates based on the specified column
dataRange.RemoveDuplicates Columns:=dupCol, Header:=xlYes
MsgBox "Duplicate rows based on Order ID removed.", vbInformation
End Sub
➧ dataRange = Range("A1").CurrentRegion captures the continuous block of data that starts at A1, including headers.
➧ RemoveDuplicates removes rows that repeat a value in the chosen column while keeping the first occurrence.
➧ Header:=xlYes tells Excel that the first row contains column names.
➧ A message box confirms the cleanup is complete.
➤ Return to Excel, press Alt + F8 , select DeleteDuplicateRows_SpecificColumn, and click Run.
➤ Rows with repeated Order ID values are deleted, leaving only the first instance of each ID and a duplicate‑free list.
Clean Rows That Contain Any Blank Cells with VBA
Rows with missing values can cause errors in summaries and reports. VBA can scan a data block, detect any row that has at least one empty cell, and delete these incomplete records in one run.
For this demonstration, the training‑attendance dataset on Sheet1 has columns Employee ID, Name, and Department. Several rows contain blanks in different columns. The macro below removes every row that includes at least one blank cell.
Steps:
➤ Press Alt + F11 , choose Insert >> Module, and paste the code:
Sub DeleteRows_WithBlanks()
Dim ws As Worksheet
Dim dataRange As Range
Dim colCount As Long
Dim i As Long
Set ws = ThisWorkbook.Sheets("Sheet1") ' sheet to clean
Set dataRange = ws.Range("A1").CurrentRegion ' table with headers in row 1
colCount = dataRange.Columns.Count ' total columns in the table
' Loop from the last data row up to row 2 (keep headers in row 1)
For i = dataRange.Rows.Count To 2 Step -1
' Count non‑blank cells in the current row
If Application.WorksheetFunction.CountA(dataRange.Rows(i)) < colCount Then
dataRange.Rows(i).EntireRow.Delete
End If
Next i
MsgBox "All rows containing blank cells have been deleted.", vbInformation
End Sub 
➧ colCount stores how many columns are in that block.
➧ The macro iterates through each row in dataRange.
➧ CountA(rowRange) counts non‑blank cells in the row. If this count is less than colCount, at least one cell is blank, so rowRange.EntireRow.Delete removes the row.
➧ A confirmation message appears after all rows with blanks are deleted.
➤ Return to Excel, press Alt + F8 , select DeleteRows_WithBlanks, and click Run.
➤ Every row that had a blank cell is removed, leaving only complete records in your dataset.
Delete Rows with Error Values Using VBA
Error cells such as #N/A or #DIV/0! can break totals and pivot tables. VBA can scan every column in each row, find any error value, and delete the entire row so only valid data remains.
For this demonstration, Sheet1 contains an order log with columns Order ID, Product, Quantity, and Revenue. Some cells hold real error results (for example, =NA() returns #N/A and =10/0 returns #DIV/0!). The macro below removes every row that contains at least one error cell.
Steps:
➤ Press Alt + F11 , choose Insert >> Module, and paste the code:
Sub DeleteRows_WithErrors()
Dim ws As Worksheet
Dim lastRow As Long, lastCol As Long
Dim i As Long, j As Long
Dim deleteRow As Boolean
Set ws = ThisWorkbook.Sheets("Sheet1") ' sheet to clean
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
' Loop from bottom to top through all rows
For i = lastRow To 2 Step -1 ' assume headers in row 1
deleteRow = False
' Check each cell in the current row
For j = 1 To lastCol
If IsError(ws.Cells(i, j).Value) Then
deleteRow = True
Exit For ' one error is enough
End If
Next j
If deleteRow Then ws.Rows(i).Delete
Next i
MsgBox "Rows containing error values deleted.", vbInformation
End Sub
➧ The outer loop scans rows from bottom to top so deletions do not shift remaining rows.
➧ The inner loop checks every cell in the row; IsError returns True for any Excel error value (e.g., #N/A, #DIV/0!, #VALUE!).
➧ When an error is found, deleteRow is set to True and the row is deleted after the inner loop.
➧ After all rows are processed, a message box confirms completion.
➤ Return to Excel, press Alt + F8 , select DeleteRows_WithErrors, and click Run.
➤ All rows containing error cells are removed, leaving a clean dataset ready for analysis.
Frequently Asked Questions
How do you delete rows with specific text in VBA?
Use AutoFilter with your criteria (e.g., Criteria1:=”=*text*”), then delete visible rows using SpecialCells(xlCellTypeVisible).EntireRow.Delete and clear the filter afterward.
Why should I loop bottom-to-top when deleting rows?
Deleting from the bottom prevents skipping rows. If you delete row 5 first, then row 6 shifts to 5. The loop continues correctly when backward.
Can I delete rows based on multiple column criteria?
Yes. Apply AutoFilter to multiple columns (e.g., Field:=1, Criteria1:=”*A*”, Field:=2, Criteria1:=”*B*”), then delete visible rows once both filters are active.
How do I delete filtered rows without triggering a warning?
Turn off alerts using Application.DisplayAlerts = False, delete visible rows, then turn alerts back on, and clear filters to complete the action silently.
Wrapping Up
Cleaning up Excel data manually can be time-consuming and prone to mistakes. With the right VBA macros, you can instantly remove rows that don’t meet your criteria, whether they’re blank, duplicated, contain error values, or hold unwanted keywords. Each method in this article is customized to a specific use case, so you can apply the one that best fits your dataset and cleanup goals.
