Method 1 – VBA Code to Delete Rows when a Single Cell Depends on a Single Value
Delete the rows with the students who got 100 in Physics.
Steps:
- Go to the Developer tab.
- Click on Visual Basic on the Code group of commands. Or press ALT+F11 on your keyboard.
Click on the image for better quality
- Go to the Insert tab in the VBA window.
- From the options, select Module.
A new module window named “Module 1” will open.
- Enter the following VBA code in the 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
Click on the image for better quality
- Save the workbook as an Excel Macro-Enabled Workbook.
Click on the image for better quality
- Return to your worksheet.
- Select the range of cells from where you want to delete the rows (without Column Headers).
- Go to the Developer tab and click on Macros.
A dialogue box named Macro will open.
- Select Delete_Rows_When_Single_Cell_Depends_on_Single_Value (The name of the Macro) and click on Run.
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 our condition is to be equal to a value (100), we have entered 5.
- The 2nd box will ask you to enter the value to which you want to compare. In this example, it is 100.
- 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).
- Click OK.
The rows that meets your condition (Marks of Physics equal to 100 in this example) will be deleted.
⧪ 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.
- You can delete the rows with Marks in Chemistry less than 40 or Marks in Mathematics greater than 80.
- When the condition is equal to some value, you can match both String and Integer type values.
- We have deleted the rows with marks equal to 100. You can also delete the rows where the name is Ross Hayes or anything.
- You can use this code to delete rows with partial matches too. Enter 7 in the 1st Input Box.
- You can delete all the rows where the surname is Hopkins.
- In the case of partial matches, it will also work for both String and Integer type values.
- 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.).
Read More: How to Use Macro to Delete Rows Based on Criteria in Excel
Method 2 – VBA Code to Delete Rows when Multiple Cells Depend on a Single Value
Delete the rows where the marks in Physics and Mathematics are greater than 80.
Steps:
- Execute the previous steps from Method 1 to open the VBA window, insert a new module, enter the following VBA code, and save the workbook.
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
Click on the image for better quality
- Select the data set from your worksheet.
As we will delete the rows where the marks in Physics and Mathematics are greater than 80, they are highlighted light brown to visualize.
- Press ALT+F8.
- Select Delete_Rows_When_Multiple_CellsDepend_on_Single_Value, and click on Run.
You will get four Input Boxes.
- The 1st box will ask for a number between 1 to 6 according to the required condition.
In this example, it is 1 (Greater than 80).
- The 2nd box will ask for the value. It’s 80.
- 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).
- The 4th 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.
In our example, it’s 1. We want the marks to be greater than 80 in both subjects.
- Click OK.
All the rows that fulfil the criteria will be deleted.
⧪ 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.
Method 3 – VBA Code to Delete Rows when Single Cell Depends on Multiple Values
Delete the rows if the Marks in Physics are equal to 100, 82, or 85.
Steps:
- Open the VBA window, insert a new module, enter the VBA code below and save the workbook.
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
Click on the image for better quality
- Select the data set from your worksheet. As I will delete the rows where the mark in Physics is 100, 85, or 80, they are highlighted in light brown to visualize.
- Press ALT+F8.
- Select Delete_Rows_When_Single_Cell_Depends_on_Multiple_Values, and click on Run.
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).
- The 2nd box will ask for the values to match. Enter them separated by commas (,).
We’ve have entered 100,85,82 (Don’t put any space in between).
- 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).
- Click OK.
All the rows that fulfil the criteria will be deleted.
⧪ Things to Remember:
- 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
Download Practice Workbook
Related Articles
- How to Filter and Delete Rows with VBA in Excel
- How to Delete Row Using Macro If Cell Contains 0 in Excel
- Excel VBA: Delete Row If Cell Is Blank
- How to Use VBA to Delete Empty Rows in Excel
- How to Apply VBA to Delete Rows with Specific Data in Excel
