How to Delete Row Based on Cell Value in Excel VBA (7 Methods)

It’s very common when we work with a large dataset that we often need to clean or organize the data. In this way, sometimes we have to delete rows, and manually doing so based on a certain value is obviously tedious. On top of that, it’s time-consuming and prone to errors.

However, Excel VBA macros can make it all easy. In this article, we will guide you through different practical VBA methods to delete rows based on different criteria. It includes deleting rows with a specific value, blank cells, numeric value, multiple conditions, user-defined inputs, filtered values, and rows in structured Excel Tables.

Sub DeleteRowsByValue()
    Dim ws As Worksheet
    Dim i As Long
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ' Loop from the last row in column B (Department) to the first row
    For i = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row To 2 Step -1
        If ws.Cells(i, "B").Value = "Finance" Then
            ws.Rows(i).Delete
        End If
    Next i
End Sub

In the following dataset, we have employee data containing different values with some blank cells. Now we are going to delete rows on specific columns based on cell value using VBA.

If you need to remove rows in your dataset that contain a specific value in a certain column, you can use the below VBA macro. The macro loops through your data and deletes any rows where the target cell exactly matches the value you specify. As a result, your sheet will only show the rows that contain the relevant data and exclude the ones with the specified value.

Steps:

➤ Press  Alt  +  F11  to open the VBA editor.
➤ Right click on your sheet name >> Click Insert >> Module

➤ In the VBA editor, insert the following VBA code. You can just copy and paste it.

Sub DeleteRowsByValue()
    Dim ws As Worksheet
    Dim i As Long
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ' Loop from the last row in column B (Department) to the first row
    For i = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row To 2 Step -1
        If ws.Cells(i, "B").Value = "Finance" Then
            ws.Rows(i).Delete
        End If
    Next i
End Sub

➤ This macro checks the target column B for the Finance specified cell value and deletes all the rows containing those values.

In case your dataset contains rows with missing information in a specific column, copy and paste the below VBA into the VBA editor. The macro checks each row and deletes any where the target cell is blank. It’s helpful when you want to prepare clean data for reporting or analysis.

Sub DeleteRowsIfCellIsBlank()
    Dim ws As Worksheet
    Dim i As Long
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ' Loop from the last used row in column D (Status) up to row 2 (to keep header)
    For i = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row To 2 Step -1
        ' Check if the trimmed cell in column D is blank
        If Trim(ws.Cells(i, "D").Value) = "" Then
            ws.Rows(i).Delete
        End If
    Next i
End Sub

Sometimes, you may need to delete rows that meet two or more criteria. The below VBA macro checks multiple columns in each row and deletes only those that match all the conditions we’ve defined.

For example, in our dataset, if we want to delete the rows where the IT department has an inactive status, the code will only show rows that don’t match both the IT department and inactive status.

Sub DeleteRowsMultipleCriteria()
    Dim ws As Worksheet
    Dim i As Long
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ' Loop from last used row in column A (Employee Name) up to row 2 (keep header)
    For i = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row To 2 Step -1
        If Trim(ws.Cells(i, "B").Value) = "IT" And Trim(ws.Cells(i, "D").Value) = "Inactive" Then
            ws.Rows(i).Delete
        End If
    Next i
End Sub

When you are working with a dataset containing numbers, you may need to delete rows based on numeric values. This VBA macro helps you remove rows where a number in a specific column meets a condition. Such as greater than, less than, or equal to a specific value. Once you run the macro, only the rows with values outside the defined numeric condition will remain in your dataset.

Sub DeleteRowsByCriteria()
    Dim ws As Worksheet
    Dim i As Long
    Set ws = ThisWorkbook.Sheets("Sheet1")
    For i = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row To 2 Step -1
        If IsNumeric(ws.Cells(i, "C").Value) Then
            If ws.Cells(i, "C").Value > 50000 Then
                ws.Rows(i).Delete
            End If
        End If
    Next i
End Sub

Now, if you want to filter data before deleting rows, you can use VBA with Filter criteria. Filter criteria let you display only the rows that meet a specific condition. The VBA uses AutoFilter to apply that condition. Then deletes all the filtered rows. After running the macro, the unfiltered rows will only remain in the dataset.

Sub DeleteRowsUsingFilter()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    With ws
        ' Apply filter on Column B (Department) for "Marketing"
        .Range("A1:D1").AutoFilter Field:=2, Criteria1:="Marketing"
        ' Delete all visible rows except the header
        On Error Resume Next ' In case no visible cells found
        .Range("A2:A" & .Cells(.Rows.Count, 2).End(xlUp).Row) _
            .SpecialCells(xlCellTypeVisible).EntireRow.Delete
        On Error GoTo 0
        ' Turn off filter
        .AutoFilterMode = False
    End With
End Sub

Instead of using the fixed cell value in the VBA, the below macro asks you to type in the value you want to remove. After you enter the specific value into the prompt, it checks the data and deletes all rows containing that specific value. As a result, your worksheet will only show the rows that don’t include the value you have entered in the prompt.

Steps:

➤ Open the VBA editor and enter the below VBA into your dataset.

Sub DeleteRowsByUserInput()
    Dim ws As Worksheet
    Dim deleteVal As String
    Dim i As Long
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ' Ask the user to enter the Department to delete
    deleteVal = InputBox("Enter the Department name to delete rows:")
    ' Exit if input is blank or cancelled
    If Trim(deleteVal) = "" Then Exit Sub
    ' Loop from bottom to top in column B (Department)
    For i = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row To 2 Step -1
        If Trim(ws.Cells(i, "B").Value) = deleteVal Then
            ws.Rows(i).Delete
        End If
    Next i
End Sub

➤ The VBA prompts to enter a specific value in the specified column. For example, we have entered Finance.
➤ Click OK.

➤ It will delete all the rows in column B that contain a Finance cell value.

If your Excel data is in an Excel table instead of a range of cells, you can delete rows based on cell value using VBA. This macro works only on Excel tables and loops through the table rows. The code will delete the rows based on your specified cell value. However, it will keep the formatting and your table structure intact.

Sub DeleteRowsInTable()
    Dim lo As ListObject
    Dim i As Long
    Set lo = ThisWorkbook.Sheets("Sheet1").ListObjects("Table1")
    For i = lo.ListRows.Count To 1 Step -1
        If lo.DataBodyRange(i, 2).Value = "Finance" Then
            lo.ListRows(i).Delete
        End If
    Next i
End Sub

Frequently Asked Questions

How to make rows disappear in Excel based on cell value?

➤ Select the data you want to filter out.
➤ Go to Data >> Filter
➤ Click the filter dropdown arrow >> Choose a filter type >> select a condition
➤ Enter the specific value or criteria in the dialog box >> Click OK.

How do you make Excel automatically delete rows with certain text?

➤ Go to Home tab >> Sort & Filter >> Filter
➤ Select Text Filters >> Contains…
➤ Enter your text >> Click OK.

How to delete rows with missing values?

➤ Enter the below VBA macro into the VBA editor.

Sub DeleteBlankRows()
    Dim i As Long
    For i = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
        If Cells(i, "A") = "" Then Rows(i).Delete
    Next i
End Sub

➤ It deletes rows with missing values in your dataset.

Wrapping Up

In this quick tutorial, we have learnt how to delete rows based on cell value criteria using VBA. Each of the VBA macros offers you a faster way to clean up the data accurately. Feel free to download the sample Excel sheet and check out how it works.