**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.

- 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
```

- Save the workbook as an
**Excel Macro-Enabled Workbook**.

- 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

**in this example) will be deleted.**

*100***⧪**** 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
```

- Select the data set from your worksheet.

As we will delete the rows where the marks inand*Physics*are greater than*Mathematics*, they are highlighted light brown to visualize.*80*

- Press
**ALT+F8.** - Select
**Delete_Rows_When_Multiple_CellsDepend_on_Single_Value,**and click on**Run**.

You will get four **Input Boxes**.

- The 1
^{st}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
```

- 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**Intege**r 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