How to Apply VBA Code to Delete Rows Based on Multiple Cell Value (3 VBA Codes)

Get FREE Advanced Excel Exercises with Solutions!

In this article, 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 entire article to understand the topic properly. Also, you can apply it to several criteria after learning it.

The GIF below is an overview of this article, which represents the process of deleting rows based on multiple cell values using VBA in Excel. In the following sections, we’ll learn more about the dataset and observe each method in detail.Overview GIF


How to Apply VBA Code to Delete Rows Based on Multiple Cell Values: 3 Easy VBA Codes 

Here we’ve got a data set with the Names of some students, along with their Marks in Physics, Chemistry, and Mathematics, of a school called Sunflower Kindergarten.

Dataset

Note: This is a basic dataset to keep things simple. In a practical scenario, you may encounter a much larger and more complex dataset.
Today our objective is to use VBA code to delete rows in Excel based on multiple cell values from this data set. Let’s explore them one by one.
Not to mention, here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience. Please leave a comment if any part of this article does not work in your version.


1. VBA Code to Delete Rows when a Single Cell Depends on a Single Value

You can use VBA code to delete rows in Excel when a single cell depends on a value from this data set.
For example, let’s try to delete the rows with the students who got 100 in Physics.

📌 Steps:

  •  At the very beginning, navigate to the Developer tab.
  •  Then, click on Visual Basic on the Code group of commands. Alternatively, press ALT+F11 on your keyboard to replicate the task.
navigating Developer tab ribbon

Click on the image for better quality

  • After that, go to the Insert tab in the VBA window.
  • Consequently, from the options available, select Module.

Visual Basic Editor

  • Instantly, a new module window called “Module 1” will open.
  • Next, insert the given VBA code in the opened Module.
Sub Delete_Rows_When_Single_Cell_Depends_on_Single_Value()
Condition = Int(InputBox _
("Enter 1 to Delete Rows with a Value Greater than Some Value: " + vbNewLine _
+ "Enter 2 to Delete Rows with a Value Greater than or Equal to Some Value: " _
+ vbNewLine + "Enter 3 to Delete Rows with a Value Less than Some Value: " + vbNewLine _
+ "Enter 4 to Delete Rows with a Value Less than or Equal to Some Value: " + vbNewLine _
+ "Enter 5 to Delete Rows with a Value Equal to Some Value: " + vbNewLine _
+ "Enter 6 to Delete Rows with a Value Not Equal to Some Value: " + vbNewLine _
+ "Enter 7 to Delete Rows with Partial Match: "))
Value = InputBox("Enter the Value: ")
Column_Number = Int(InputBox("Enter the Number of the Column where the Criteria will be Applied: "))
If Condition = 1 Then
    Value = Int(Value)
    For i = 1 To Selection.Rows.Count
        If Selection.Cells(i, Column_Number) > Value Then
            Selection.Cells(i, 1).EntireRow.Delete
            i = i - 1
        End If
    Next i    
ElseIf Condition = 2 Then
    Value = Int(Value)
    For i = 1 To Selection.Rows.Count
        If Selection.Cells(i, Column_Number) >= Value Then
            Selection.Cells(i, 1).EntireRow.Delete
            i = i - 1
        End If
    Next i    
ElseIf Condition = 3 Then
    Value = Int(Value)
    For i = 1 To Selection.Rows.Count
        If Selection.Cells(i, Column_Number) < Value Then
            Selection.Cells(i, 1).EntireRow.Delete
            i = i - 1
        End If
    Next i    
ElseIf Condition = 4 Then
    Value = Int(Value)
    For i = 1 To Selection.Rows.Count
        If Selection.Cells(i, Column_Number) <= Value Then
            Selection.Cells(i, 1).EntireRow.Delete
            i = i - 1
        End If
    Next i    
ElseIf Condition = 5 Then
    For i = 1 To Selection.Rows.Count
        If VarType(Selection.Cells(i, Column_Number)) <> 8 Then
            On Error Resume Next
            Value = Int(Value)
        End If
        If Selection.Cells(i, Column_Number) = Value Then
            Selection.Cells(i, 1).EntireRow.Delete
            i = i - 1
        End If
    Next i
ElseIf Condition = 6 Then
    For i = 1 To Selection.Rows.Count
        If VarType(Selection.Cells(i, Column_Number)) <> 8 Then
            On Error Resume Next
            Value = Int(Value)
        End If
        If Selection.Cells(i, Column_Number) <> Value And Selection.Cells(i, Column_Number) <> "" Then
            Selection.Cells(i, 1).EntireRow.Delete
            i = i - 1
        End If
    Next i
ElseIf Condition = 7 Then
    For i = 1 To Selection.Rows.Count
        If VarType(Selection.Cells(i, Column_Number)) <> 8 Then
            On Error Resume Next
            Cell_Value = Str(Selection.Cells(i, Column_Number))
        Else
            Cell_Value = Selection.Cells(i, Column_Number)
        End If
        For j = 1 To Len(Cell_Value)
            If Mid(Cell_Value, j, Len(Value)) = Value Then
                Selection.Cells(i, 1).EntireRow.Delete
                i = i - 1
                Exit For
            End If
        Next j
    Next i
End If
End Sub
VBA code to delete rows based on multiple cell value

Click on the image for better quality

  • Hence, save the workbook as an Excel Macro-Enabled Workbook.
Saving Workbook as Macro enabled format

Click on the image for better quality

  • Return to your worksheet.
  • Now, select the range of cells from where you want to delete the rows (without Column Headers).

selecting dataset to apply vba macro to delete rows

  • Again, go to the Developer tab and click on the Macros option. Also, you can press ALT+F8 on your keyboard.

clicking on macro option

  • A dialogue box called Macro will open. Select Delete_Rows_When_Single_Cell_Depends_on_Single_Value (The name of the Macro) and click on Run.

choosing right macro to delete rows

  • Therefore, you will get three Input Boxes. The 1st box will ask you to enter a number between 1 to 7 depending on your condition.

As my condition is to be equal to a value (100), I have entered 5.

deleting rows with a value equal to some value

  • The 2nd box will ask you to enter the value to which you want to compare. In this example, it is 100.

Entering value to compare

  • And the 3rd Input Box will ask for the column number where the condition will be applied. In this example, it’s 2 (Marks in Physics).

Column number in the Dataset

  • Then click OK.
  • You will find the rows that satisfy your condition (Marks of Physics equal to 100 in this example) deleted automatically.

Rows got deleted based on multiple cell value

Things to Remember:

  • You can also use this code to delete rows with greater than, less than, or not equal to a value. Select your required condition from the 1st Input Box.
  • For example, you can delete the rows with Marks in Chemistry less than 40 or Marks in Mathematics greater than 80. Try yourself.
  • When the condition is equal to some value, you can match both String and Integer type values.
  • For example, here we’ve deleted the rows with marks equal to 100. You can also delete the rows where the name is Ross Hayes or anything. Try yourself.
  • You can use this code to delete rows with partial matches too. Enter 7 in the 1st Input Box.
  • For example, you can delete all the rows where the surname is Hopkins. Try yourself.
  • In the case of partial matches, it will also work for both String and Integer type values.
  • For example, you can delete the rows with the surname Hopkins, you can also delete the rows that contain a 5 within the mark in Physics (45, 50, 523, 53, 65, etc.). Try yourself.

Read More: How to Use Macro to Delete Rows Based on Criteria in Excel


2. VBA Code to Delete Rows when Multiple Cells Depend on a Single Value

Now we’ll develop a Macro to delete rows when multiple cells depend on a single value.
For example, let’s try to delete the rows where the marks in Physics and Mathematics are greater than 80.

📌 Steps:

Sub Delete_Rows_When_Multiple_Cells_Depend_on_Single_Value()
Condition = Int(InputBox("Enter 1 to Delete Rows with a Value Greater than Some Value: " + vbNewLine + "Enter 2 to Delete Rows with a Value Greater than or Equal to Some Value: " + vbNewLine + "Enter 3 to Delete Rows with a Value Less than Some Value: " + vbNewLine + "Enter 4 to Delete Rows with a Value Less than or Equal to Some Value: " + vbNewLine + "Enter 5 to Delete Rows with a Value Equal to Some Value: " + vbNewLine + "Enter 6 to Delete Rows with a Value Not Equal to Some Value: " + vbNewLine + "Enter 7 to Delete Rows with Partial Match: "))
Value = InputBox("Enter the Value: ")
Column_Numbers = InputBox("Enter the Number of the Columns where the Criteria will be Applied: ")
Column_Numbers = Split(Column_Numbers, ",")
Condition_Type = Int(InputBox("Enter 0 for OR Type Criteria: " + vbNewLine + "OR" + vbNewLine + "Enter 1 for AND Type Criteria: "))
Dim Count As Integer
If Condition = 1 Then
    Value = Int(Value)
    For i = 1 To Selection.Rows.Count
        Count = 0
        For j = 0 To UBound(Column_Numbers)
            If Selection.Cells(i, Int(Column_Numbers(j))) > Value Then
                Count = Count + 1
            End If
        Next j
        If Condition_Type = 0 Then
            If Count > 0 Then
                Selection.Cells(i, 1).EntireRow.Delete
                i = i - 1
            End If
        Else
            If Count = UBound(Column_Numbers) + 1 Then
                Selection.Cells(i, 1).EntireRow.Delete
                i = i - 1
            End If
        End If
    Next i    
ElseIf Condition = 2 Then
    Value = Int(Value)
    For i = 1 To Selection.Rows.Count
        Count = 0
        For j = 0 To UBound(Column_Numbers)
            If Selection.Cells(i, Int(Column_Numbers(j))) >= Value Then
                Count = Count + 1
            End If
        Next j
        If Condition_Type = 0 Then
            If Count > 0 Then
                Selection.Cells(i, 1).EntireRow.Delete
                i = i - 1
            End If
        Else
            If Count = UBound(Column_Numbers) + 1 Then
                Selection.Cells(i, 1).EntireRow.Delete
                i = i - 1
            End If
        End If
    Next i
    
ElseIf Condition = 3 Then
    Value = Int(Value)
    For i = 1 To Selection.Rows.Count
        Count = 0
        For j = 0 To UBound(Column_Numbers)
            If Selection.Cells(i, Int(Column_Numbers(j))) < Value Then
                Count = Count + 1
            End If
        Next j
        If Condition_Type = 0 Then
            If Count > 0 Then
                Selection.Cells(i, 1).EntireRow.Delete
                i = i - 1
            End If
        Else
            If Count = UBound(Column_Numbers) + 1 Then
                Selection.Cells(i, 1).EntireRow.Delete
                i = i - 1
            End If
        End If
    Next i    
ElseIf Condition = 4 Then
    Value = Int(Value)
    For i = 1 To Selection.Rows.Count
        Count = 0
        For j = 0 To UBound(Column_Numbers)
            If Selection.Cells(i, Int(Column_Numbers(j))) <= Value Then
                Count = Count + 1
            End If
        Next j
        If Condition_Type = 0 Then
            If Count > 0 Then
                Selection.Cells(i, 1).EntireRow.Delete
                i = i - 1
            End If
        Else
            If Count = UBound(Column_Numbers) + 1 Then
                Selection.Cells(i, 1).EntireRow.Delete
                i = i - 1
            End If
        End If
    Next i    
ElseIf Condition = 5 Then
    Value = Int(Value)
    For i = 1 To Selection.Rows.Count
        If VarType(Selection.Cells(i, 1)) <> 8 Then
            On Error Resume Next
            Value = Int(Value)
        End If
        Count = 0
        For j = 0 To UBound(Column_Numbers)
            If Selection.Cells(i, Int(Column_Numbers(j))) = Value Then
                Count = Count + 1
            End If
        Next j
        If Condition_Type = 0 Then
            If Count > 0 Then
                Selection.Cells(i, 1).EntireRow.Delete
                i = i - 1
            End If
        Else
            If Count = UBound(Column_Numbers) + 1 Then
                Selection.Cells(i, 1).EntireRow.Delete
                i = i - 1
            End If
        End If
    Next i
ElseIf Condition = 6 Then
    Value = Int(Value)
    For i = 1 To Selection.Rows.Count
        If VarType(Selection.Cells(i, 1)) <> 8 Then
            On Error Resume Next
            Value = Int(Value)
        End If
        Count = 0
        For j = 0 To UBound(Column_Numbers)
            If Selection.Cells(i, Int(Column_Numbers(j))) <> Value And Selection.Cells(i, Int(Column_Numbers(j))) <> "" Then
                Count = Count + 1
            End If
        Next j
        If Condition_Type = 0 Then
            If Count > 0 Then
                Selection.Cells(i, 1).EntireRow.Delete
                i = i - 1
            End If
        Else
            If Count = UBound(Column_Numbers) + 1 Then
                Selection.Cells(i, 1).EntireRow.Delete
                i = i - 1
            End If
        End If
    Next i
ElseIf Condition = 7 Then
    For i = 1 To Selection.Rows.Count
        Count = 0
        For j = 0 To UBound(Column_Numbers)
            If VarType(Selection.Cells(i, Int(Column_Numbers(j)))) <> 8 Then
               On Error Resume Next
               Cell_Value = Str(Selection.Cells(i, Int(Column_Numbers(j))))
            Else
                Cell_Value = Selection.Cells(i, Int(Column_Numbers(j)))
            End If
            For k = 1 To Len(Cell_Value)
                If Mid(Cell_Value, k, Len(Value)) = Value Then
                    Count = Count + 1
                    Exit For
                End If
            Next k
        Next j
        If Condition_Type = 0 Then
            If Count > 0 Then
                Selection.Cells(i, 1).EntireRow.Delete
                i = i - 1
            End If
        Else
            If Count = UBound(Column_Numbers) + 1 Then
                Selection.Cells(i, 1).EntireRow.Delete
                i = i - 1
            End If
        End If
    Next i
End If
End Sub
VBA code to delete rows based on multiple cells value

Click on the image for better quality

Then select the data set from your worksheet.
As I’ll delete the rows where the marks in Physics and Mathematics are greater than 80, they are highlighted light brown to visualize.

selecting dataset without column headings

Then press ALT+F8, select Delete_Rows_When_Multiple_CellsDepend_on_Single_Value, and click on Run.

working on Macro dialog box

You will get four Input Boxes. The 1st one will ask for a number between 1 to 6 according to the required condition.
In this example, it is 1 (Greater than 80).

deleting rows with Value greater than some value

The 2nd box will ask for the value. It’s 80.

value to compare

The 3rd box will ask for the numbers of the columns where the condition will be applied.
In this example, it’s 2,4 (Marks in Physics and Marks in Mathematics).
Separate them by a comma (Don’t put any space in between).

entering column number for applying condition

And the final box will ask for the OR Type or the AND Type criteria.
Enter 0 for OR Type criteria.
And enter 1 for AND Type criteria.
Here it’s 1. We want the marks to be greater than 80 in both subjects.

AND type

Finally, click OK. You’ll find all the rows that fulfill the criteria deleted automatically.

Final output

Things to Remember:

  • You can use this code for all types of conditions like greater than, less than, equal to, not equal to, partial match, etc. Select your required one from the 1st Input Box (Same as Code 1).
  • Equal to, not equal to and the partial match will work for String and Integer type values (Same as the 1st code).
  • You can apply the condition on any column within your data set. Enter them in Input Box 3.
  • You can apply both AND Type and OR Type criteria to the data set. Select accordingly on Input Box 4.

3. VBA Code to Delete Rows when Single Cell Depends on Multiple Values

Now, we’ll develop a VBA code to delete rows when a single cell depends on multiple values.
For example, let’s try to delete the rows if the Marks in Physics are equal to 100, 82, or 85.

📌 Steps:

Sub Delete_Rows_When_Single_Cell_Depends_on_Multiple_Values()
Condition = Int(InputBox("Enter 1 for an Exact Match: " + vbNewLine + "OR" + vbNewLine + "Enter 2 for a Partial Match:"))
Values = InputBox("Enter the Values: ")
Values = Split(Values, ",")
Column_Number = Int(InputBox("Enter the Number of the Columns where the Criteria will be Applied: "))
If Condition = 1 Then
    For i = 1 To Selection.Rows.Count
        For j = 0 To UBound(Values)
            If VarType(Selection.Cells(i, Column_Number)) <> 8 Then
                On Error Resume Next
                If Selection.Cells(i, Column_Number) = Int(Values(j)) Then
                    Selection.Cells(i, Column_Number).EntireRow.Delete
                    i = i - 1
                    Exit For
                End If
            Else
                If Selection.Cells(i, Column_Number) = Values(j) Then
                    Selection.Cells(i, Column_Number).EntireRow.Delete
                    i = i - 1
                    Exit For
                End If
            End If
        Next j
    Next i   
ElseIf Condition = 2 Then
    For i = 1 To Selection.Rows.Count
        For j = 0 To UBound(Values)
            If VarType(Selection.Cells(i, Column_Number)) <> 8 Then
                On Error Resume Next
                If InStr(Selection.Cells(i, Column_Number), Int(Values(j))) = 1 Then
                    Selection.Cells(i, Column_Number).EntireRow.Delete
                    i = i - 1
                    Exit For
                End If
            Else
                If InStr(Selection.Cells(i, Column_Number), Values(j)) Then
                    Selection.Cells(i, Column_Number).EntireRow.Delete
                    i = i - 1
                    Exit For
                End If
            End If
        Next j
    Next i
End If
End Sub
VBA Code to delete rows based on multiple cell value

Click on the image for better quality

  • Then select the data set from your worksheet. As I’ll delete the rows where the mark in Physics is 100, 85, or 80, they are highlighted in light brown to visualize.

selecting dataset to apply vba code

  • Then press ALT+F8, select Delete_Rows_When_Single_Cell_Depends_on_Multiple_Values, and click on Run.

selecting macro to perform deleting rows

You will get three Input Boxes. The 1st one will ask for a number between 1 to 2 according to the required condition.
1 for an Exact Match.
2 for a Partial Match.
In this example, it is 1 (Exact Match).

looking for partial match

The 2nd box will ask for the values to match. Enter them separated by commas (,).
Here I have entered 100,85,82 (Don’t put any space in between).

entering value to match

The 3rd box will ask for the number of the column where the condition will be applied.
In this example, it’s 2 (Marks in Physics).

entering column number to apply criteria

  • Then click OK.

Afterward, you will get the rows that fulfill the desired criteria deleted automatically.

final output

Things to Remember:

  • Besides, you can only apply exact match and partial match conditions in this code. No other conditions like greater than, less than, etc.
  • This will be applicable for both String and Integer data types.

Read More: Excel VBA to Delete Row If Cell Contains Partial Text


Practice Section

For doing practice by yourself we have provided a Practice section like the one below in each sheet. Please do it by yourself.

Practice Section


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.


Conclusion

Using these methods, you can incorporate VBA code to delete rows in Excel based on multiple cell values. Do you have any questions? Feel free to ask us.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo