Excel VBA to Delete Row Based on Cell Value

While working in Excel, you may face thousands of rows to work with and sometimes you may need to delete rows based on cell value. You can do it quite easily with the help of Excel VBA, as I have done in the earlier video, where I have combined If Statement and For Loop to delete rows containing Cable as cell value within range B5:E11. In this article, I will show you various examples and methods to use Excel VBA to Delete rows based on Cell Value

Fortunately, many of us use Excel in our business organizations. In any business organization, we use Excel to organize data as needed and make databases for the future. To assist users in creating and manipulating data, it provides a wide range of features and operations. The ability to delete rows based on cell value is one of Excel’s useful features. Deleting rows is a useful feature in Excel that can be used to visually divide data and make it simpler to read. However, I have used Microsoft Office 365 for the purpose of demonstration, and you can use other versions according to your preferences.

Excel comes with a programming language called VBA. Users can automate processes, write original functions, and change data thanks to it. One of the formatting options that may be accessed and changed using Excel VBA is deleting rows based on cell value. Users can easily modify their dataset by deleting rows based on cell value with this attribute. Hence, read through the article to learn more and save time.

Getting Started with VBA in Excel

A macro is a sequence of instructions. This sequence of instructions automates some aspects of Excel that you do regularly. When you’re automating some of your work in Excel using macros, you can perform more efficiently and with fewer errors. For example, you may create a macro to format and print your month-end sales report. After the macro is developed, you can then execute the macro to perform many time-consuming procedures automatically.

A user doesn’t have to be a power user to create and use simple VBA macros. Casual users can simply turn on Excel’s macro recorder: Excel will record your actions and convert them into a VBA macro. When you execute this macro, Excel performs the actions again.

i. Enabling the Developer Tab in Excel

All of the Office applications have a Developer tab in Excel Ribbon. You may find it hidden usually. You need to enable it through the settings option. We are more focused on the usage and application of Microsoft Excel here. But it is almost the same for any other Office application. However, when you’re planning to work with VBA macros, you’ve to make sure that the Developer tab is present on the Excel Ribbon.

ii. Launching VBA Editor

Once you have the Developer tab, you will have access to the VBA editor window that will enable you to add, edit, or remove VBA code. Individual VBA codes are generally stored in a place called a module. It is usually a good practice to store different codes in different modules. Here is how you can create a module in the VBA window.

Firstly, hold the Alt + F11 keys in Excel, which opens the Microsoft Visual Basic Applications
Secondly, click the Insert button and select Module from the menu.

Thirdly, it will create a module. However, it will look like the one below.

Excel VBA to Delete Row Based on Cell Value: 5 Examples

In this tutorial, I will show you how to delete a row based on cell value in Excel VBA. Here, I have used 5 different ways to make you understand the scenario properly. For the purpose of demonstration, I have used the following sample dataset. Here, the dataset contains the “Net Sales of Products”. However, it contains the student ID, Name of the Salesman, Product, and Net Sales.

1. Combining If Statement and For Loop to Delete Rows

In this part, I will show how to delete rows based on cell value with the help of the If statement and For loop. Here, I have combined them in a VBA code. It will search for the product Cable and if the code finds the text then it will delete the rows. Let’s get started.

Initially, create a new module in the VBA window.
Then, insert the following code in the module.

'Combination of If Statement and For loop
'Declaring Sub-procedure
Sub Delete_Rows_with_If_and_For()
'Declaring variables
Dim cell As Range
For Each cell In Range("B5:E11")
    'We will delete rows containing Cable
    If cell.Value = "Cable" Then
        cell.EntireRow.Delete
    End If
Next cell
End Sub

In this code, I have used a For loop within Range B5:E11. Here, if the cell value is Cable within this range then it will delete the rows.

Finally, press the F5 key or the Run button to run the code.

2. Utilizing Filter Feature to Delete Rows with Excel VBA

Additionally, you can use the Filter feature in Excel VBA to filter the data based on cell values and then delete the rows that meet the filter criteria. In this part, I will show you the process of utilizing the Filter option with the help of Excel VBA. So, let’s get started to solve it.

In the beginning, create a new module in the VBA window.
Then, insert the following code in the module.

'Use of Filter Feature in VBA
'Declaring Sub-procedure
Sub Delete_Rows_Using_Filter_Feature()
'Declaring variables
Dim WB As Worksheet
'Set the worksheet where you want to apply the VBA
  Set WB = ThisWorkbook.Worksheets("Filter Feature with VBA")
  WB.Activate
  'Delete any filters if exists
  On Error Resume Next
    WB.ShowAllData
  On Error GoTo 0
'Select Range and column no. where you want to apply filter
'Provide criteria through which you want to filter
  WB.Range("B5:E11").AutoFilter Field:=3, Criteria1:="Cable"
'Delete rows from the Range which matches the criteria
  Application.DisplayAlerts = False
    WB.Range("B5:E11").SpecialCells(xlCellTypeVisible).Delete
  Application.DisplayAlerts = True
'Now, clear filter
  On Error Resume Next
    WB.ShowAllData
  On Error GoTo 0
End Sub

In this code, I have set a worksheet named Filter Feature with VBA and a Range of B5:E11. Here, I have selected AutoFilter Field:=3 in order to search for Criteria1:=”Cable” in the Product column of the dataset.

Finally, press the F5 key or the Run button to run the code.

3. Delete Rows If the Cell Is Empty with Excel VBA

You can use Excel VBA to delete rows if a cell is empty. Here, I have some blank cells in the dataset. However, with the help of VBA, I will delete rows if cells are empty within the provided Range. Hence, follow the below part in order to learn more.

Firstly, create a new module in the VBA window.
Secondly, insert the following code in the module.

'Delete Rows if Cell is Empty
'Declaring Sub-procedure
Sub Delete_Rows_if_Cell_is_Empty()
'Select Range from where you want to delete rows containing empty cells
Range("B5:E11").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

Thirdly, press the F5 key or the Run button to run the code and the output will appear as below.

4. Excel VBA to Delete Blank Rows

In Excel, you may have blank rows in your worksheet that are unnecessary and may make it difficult to read or analyze your data. You can use Excel VBA to delete these blank rows automatically. For the purpose of demonstration, I have modified the given dataset a bit.

Here, I have added some blank rows in between multiple rows of the previous dataset. What you have to do is to remove these blank rows with the help of Excel VBA. So, let’s get started.

Initially, create a new module in the VBA window.
Then, insert the following code in the module.

'Deleting Blank Rows
'Declaring Sub-procedure
Sub Delete_Blank_Rows()
'Declaring Variables
Dim BlRw As Range
For Each BlRw In Range("B5:E15")
    If Application.WorksheetFunction.CountA(BlRw.EntireRow) = 0 Then
        BlRw.EntireRow.Delete
    End If
Next BlRw
End Sub

Here, I have used a For loop within Range B5:E15. Then, I stated an If statement to check whether the rows are empty or not with the help of the COUNTA function. The code will delete the empty rows.

Finally, press the F5 key or the Run button to run the code and you will get your desired output.

5. Delete Rows Based on Specific Criteria Set by User

Because of the dense data and complicated structure, Excel worksheets can be difficult to read and find data at times. For this reason, you can set specific criteria according to your choice. In this part, I will show you the process of deleting rows based on specific criteria set by the user. Here, an input box will appear in order to search data and delete the rows containing the item.

Firstly, select the dataset.
Secondly, go to the Insert tab and select Table.

Thirdly, hit the OK button.

After that, a table will be created as the image below.

Now, create a new module in the VBA window.
Then, insert the following code in the module.

'Deleting Rows Based on Specific Criteria Set by User
'Declaring Sub-procedure
Sub Delete_Rows_Based_on_Criteria_by_User()
'Declaring Variables
Dim LstObj As ListObject
Dim LgRows As Long
Dim FltrCriteria As Variant
  'Set the sheet name and Table as reference
  Set LstObj = ThisWorkbook.Worksheets("Specific Criteria Set by User").ListObjects(1)
  'Activate sheet that Table is on
  LstObj.Parent.Activate
  'Now, Clear if any filters exists
  LstObj.AutoFilter.ShowAllData
  'Enter the Filter criteria
  FltrCriteria = Application.InputBox(Prompt:="Enter the filter criteria for the Product column." _
                                    & vbNewLine & "Keep the box empty if you want to filter for blanks.", _
                                    Title:="Filter Criteria", _
                                    Type:=2)
  'Exit if user hits the Cancel button
  If FltrCriteria = False Then Exit Sub
  'Apply the Filter
  LstObj.Range.AutoFilter Field:=3, Criteria1:=FltrCriteria
  'Count Rows & display message
  On Error Resume Next
    LgRows = WorksheetFunction.Subtotal(103, LstObj.ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible))
  On Error GoTo 0
    'The following part will Delete Filtered Rows
    Application.DisplayAlerts = False
      LstObj.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
    Application.DisplayAlerts = True
    'Now, Clear the Filter
    LstObj.AutoFilter.ShowAllData
End Sub

In this code, I have inserted a Filter criterion with the help of InputBox. You need to put the filter value in the box and the code will delete the rows which match the value.

Lastly, press the F5 key or the Run button to run the code and the output will appear as below.

Excel VBA to Delete Rows Based on Cells in Another Sheet

In this section, I will delete rows based on cells in another sheet with the help of Excel VBA. For this purpose, I have selected the “Dataset” sheet from where data will be compared. Again, another sheet named “Delete Row On Another Sheet” will check it and delete rows that match the previous one. Hence, read the below part to learn more. For the purpose of demonstration, I have changed the ID column of the dataset slightly.

First of all, create a new module in the VBA window.
Then, insert the following code in the module.

'Deleting Rows Based on Cells in Other Sheet
'Declaring Sub-procedur
Sub Delete_Rows_Based_On_Cells_in_Other_Sheet()
'Declaring Variables
    Dim ws11 As Worksheet, ws22 As Worksheet
    Dim lasstRow As Long, i As Long, j As Long
    Dim DeleteRows As Range, cell As Range
    'Set "Sheet1" to the name of the sheet from where you want to delete rows from
    Set ws11 = Sheets("Based on Cells in Other Sheet")
    'Set "Sheet2" to the name of the sheet which you want to compare with
    Set ws22 = Sheets("Dataset")
    'Consider column B has data and finding the last row in column B
    lasstRow = ws11.Cells(ws11.Rows.Count, "B").End(xlUp).Row
    'My data started with row 5
    For i = 5 To lasstRow
    'Assuming column B has data and finding the last row in column B
        For j = 1 To ws22.Cells(ws22.Rows.Count, "B").End(xlUp).Row
        'Considering the cells which will be compared are in column B in both sheets
            If ws11.Cells(i, 2) = ws22.Cells(j, 2) Then
                If DeleteRows Is Nothing Then
                    Set DeleteRows = ws11.Rows(i)
                Else
                    Set DeleteRows = Union(DeleteRows, ws11.Rows(i))
                End If
                Exit For
            End If
        Next j
    Next i
    If Not DeleteRows Is Nothing Then DeleteRows.Delete
End Sub

In this code, I have set the Dataset sheet to compare with the worksheet named Based on Cells in Other Sheet. Afterward, I started two For loops, and with the help of two If statements, I compared the values of column B between two sheets. If the value matches, then the code will delete the rows.

Finally, press the F5 key or the Run button to run the code and you will receive your desired result.

Excel VBA to Delete Row on Another Sheet

Suppose, you have multiple worksheets in a particular workbook. Sometimes, it becomes difficult to find a particular worksheet among them. Then, you can easily delete rows on another sheet while you are staying on a different sheet. You need to know the Name of the Sheet and Range which you want to remove. The process is simple. Let’s say, I am now in the “Dataset” sheet and I want to delete rows on another sheet named “Delete Row On Another Sheet”. Here, I have selected a Range B7:E10 to delete rows.

Initially, create a new module in the VBA window.
Then, insert the following code in the module.

'Deleting Row on Another Sheet
'Declaring Sub-procedure
Sub DeleteRows()
'Select the sheet name from where you want to delete rows and the Range
    ThisWorkbook.Sheets("Delete Row On Another Sheet").Range("B7:E10").Delete xlUp
End Sub

Next, press the F5 key or the Run button to run the code and you will receive your desired result.

How to Remove Duplicate Rows in Excel VBA

In the past, removing duplicate data was a manual task. But now Excel provides some handy tools that make it easier to locate and delete duplicate values from a table or from a range. In a small range, it is easy to identify the duplicate values, but in large data that has thousands of rows, it will take a huge time and effort to locate the duplicate values and then delete them. You can insert VBA codes to serve the purpose. Hence, go through the rest of the part to complete the task properly.

Here, I have changed the dataset a bit. For the purpose of demonstration, I have copied some rows multiple times in order to create duplicate rows.

In the beginning, create a new module in the VBA window.
Then, insert the following code in the module.

'Removing Duplicate Rows
'Declaring Sub-procedure
Sub Remove_Duplicate_Rows()
'Define the range from where you want to remove duplicates
Range("B5:E11").RemoveDuplicates Columns:=4
End Sub

In this code, I have selected a Range of B5:E11. Here, Columns:=4 indicates the column index number is 4. The code will remove if it finds any duplicate rows within this range.

Next, press the F5 key or the Run button to run the code and you will receive your desired result.

Excel VBA to Delete Entire Row Based on Cell Value

Sometimes we need to remove an entire specific row from a huge dataset of rows in Excel. When the cells in the dataset are carrying values, then we should be really careful before deleting them. Implementing VBA macro is the most effective, quickest, and safest method to run any operation in Excel.

Initially, create a new module in the VBA window.
Then, insert the following code in the module.

'Declaring Sub-procedure
Sub Delete_Entire_Row_Based_on_Cell_Value()
'Declaring variables
Dim s_row As Long
Dim c_i As Long
'Provide the last row number
s_row = 12
For c_i = s_row To 1 Step -1
   If Cells(c_i, 4) = "Cable" Then
      Rows(c_i).Delete
   End If
Next
End Sub

In this code, I stated a For loop and an If statement. The If statement will check for Cable in the Products column. For this reason, I put 4 as the column index number.

Next, press the F5 key or the Run button to run the code and you will receive your desired result.

How to Delete Row and Shift Up with VBA in Excel

Sometimes we need to delete a specific row from a certain row number in Excel and shift the other rows up to the initial position. Here, I have started from B5 and selected Range up to B11. The code will search for empty cells in Column B and delete them. Finally, the rows below will be shifted up.

First, create a new module in the VBA window.
Then, insert the following code in the module.

'Deleting Row with VBA and Shift Up
'Declaring Sub-procedure
Sub Delete_Row_and_Shift_Up()
'Declaring Variables
    Dim Rw As Double
    Rw = Worksheets("Delete Row and Shift Up").Range("B11").End(xlUp).Row
    Worksheets("Delete Row and Shift Up").Range("B5:B" & Rw).SpecialCells _
    (xlCellTypeBlanks).EntireRow.Delete shift:=xlUp
End Sub

In the code, I have selected column B as Range. Here, I have provided B11 as the last row and B5 as the starting row. The code will delete rows if any cell within the range is empty and shift:=xlUp will move the remaining rows upwards.

After that, press the F5 key or the Run button to run the code and you will receive your desired result.

Excel VBA Code to Delete Rows Based on Multiple Cell Values

In this section, I’ll show you the VBA code to delete rows in Excel based on multiple cell values. You’ll learn to delete rows when multiple cells depend on a single value, as well as when single cells depend on multiple values. So, go through the below part to understand the topic properly. Also, you can apply it to several criteria after learning it. For the purpose of demonstration, I have used the same sample dataset.

I. Delete Rows If the Cell Value Is Not One of the Desired Values

One interesting thing is that you can easily delete rows if the cell value is not one of the desired values easily with Excel VBA. In this part, I have selected column D to search for my desired values. Additionally, I have chosen Cable, Fridge, and TV as my desired values. So, the following code will delete rows other than these values.

Initially, create a new module in the VBA window.
Then, insert the following code in the module.

'Delete Rows If Cell Value is Not One of Desired Values
'Declaring Sub-procedure
Sub Delete_Rows_If_Cell_Value_is_not_one_of_Desired_Values()
'Declaring variables
Dim Prt As Long
For Prt = Cells(Rows.Count, "D").End(xlUp).Row To 5 Step -1
    If Cells(Prt, "D").Value <> "Cable" And Cells(Prt, "D").Value <> "Fridge" And Cells(Prt, "D") <> "TV" Then
        Rows(Prt).EntireRow.Delete
    End If
Next
End Sub

In this code, I have started a For loop and an If statement within column D. The code will check for Cable, Fridge, and TV and delete rows that don’t contain these values.

Lastly, press the F5 key or the Run button to run the code and you will receive your desired result.

II. Deleting Rows Based on Multiple Criteria

Moreover, you can delete rows based on multiple criteria in Excel. It means you can set more than one criterion in order to delete rows from your dataset. For the purpose of demonstration, I have selected 14107 from column B and TV from column D to set my criteria. So, if you run the code, it will delete rows that match both of the criteria.

In the beginning, create a new module in the VBA window.
Then, insert the following code in the module.

'Delete Rows Based on multiple Criteria
'Declaring Sub-procedure
Sub Delete_Rows_based_on_Multiple_Criteria()
'Declaring variables
    Dim LastRow As Long
    Dim p As Long
    'Get the last row in column B
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
    'Start from the last row and loop upwards
    For p = LastRow To 5 Step -1
        'Check if both conditions are met
        If Cells(p, "B") = "14107" And Cells(p, "D") = "TV" Then
            'Delete the entire row
            Rows(p).Delete
        End If
    Next p
End Sub

In this code, the If statement will search for 14107 in column B and TV in column D. With the help of For loop, every cell will be checked accordingly within the range. The code will delete rows that meet both of the conditions.

Lastly, press the F5 key or the Run button to run the code and you will receive your desired result.

III. Delete Rows That Is Empty in Column B and Not-Empty in Column D

In this section, I will show you an exceptional scenario. Here, you will be able to delete rows that are empty in column B and not empty in column D. However, you can set any value from column D. Here, I have chosen TV as not empty cell value. Hence, go through the following part to learn more about the process.

First, create a new module in the VBA window.
Then, insert the following code in the module.

'Deleting Rows which is Empty in Column B and Not-Empty in Column D
'Declaring Sub-procedure
Sub Delete_Rows_based_on_Empty_Non_Empty_Cell()
'Declaring variables
    Dim LastRow As Long
    Dim p As Long
    'Get the last row in column B
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
    'Start from the last row and loop upwards
    For p = LastRow To 5 Step -1
        'Check if both conditions are met
        If Cells(p, "B") = "" And Cells(p, "D") = "TV" Then
            'Delete the entire row
            Rows(p).Delete
        End If
    Next p
End Sub

In this code, the If statement will search for Blank cells in column B and TV in column D. With the help of For loop, every cell will be checked accordingly within the range. The code will delete rows that meet both of the conditions.

Next, press the F5 key or the Run button to run the code and you will receive your desired result.

Things to Remember

You can change the Range in each code to change the location where you want to apply the code.
You should give the Column number according to your dataset. This number indicates the column from which you want to search for a specific cell value.
In Method-5, you have to insert the value which you want to search in the InputBox.
You can delete rows if a cell is empty and if your dataset contains empty rows. Check the codes from the methods.
However, you can change the code according to your preference.

Download Practice Workbook

You can download the workbook used for the demonstration from the download link below.

VBA Delete Row Based on Cell Value.xlsm

Conclusion

These are all the steps you can follow in Excel VBA to delete rows based on cell value. Overall, in terms of working with time, we need this for various purposes. I have shown multiple methods with their respective examples. Here, I have demonstrated several situations to delete rows which include a combination of If and For loop, if the cell is empty, if rows are blank, and based on specific criteria. Moreover, you can easily start your journey with VBA and delete rows based on various types of cell values with the help of VBA. Additionally, I have shown how to delete rows on another sheet and remove duplicate rows.

However, you will be able to delete the entire row based on a cell value and delete rows based on multiple cell values in the later part of the article. Hopefully, you can now easily make the needed adjustments and use the codes according to your needs.

I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.

Get FREE Advanced Excel Exercises with Solutions!