Instructions on 2 simple ways to delete conditional rows in Excel

Instructions on 2 simple ways to delete conditional rows in Excel Picture 1

Method 1: Delete conditional rows in Excel using Find and Replace

In addition to the search and replace feature, the Find and Replace function is also used to conditionally delete rows in Excel. Readers do the following:

Step 1: Select the data area that has the row you need to delete.

Step 2: Press the key combination Ctrl + F to open the Find and Replace dialog box .

Step 3: In the Find and Replace dialog box , in the Find what box enter the value of the conditional cell you want to delete, then select Find All . In this example we will delete the cells with the value 'fat milk'.

Step 4: Press the key combination Ctrl + A to select all search results at the bottom of the Find and Replace dialog box. Then close the dialog box.

Step 5: Right-click one of the selected cells, then select Delete , then select Table Rows.

Thus, with just a few simple operations, the lines containing the value 'Fat Milk' have been deleted from the data table.

Method 2: Delete conditional rows in Excel using VBA code

In addition to the Find and Replace function , you can conditionally delete rows using VBA code. Readers do the following:

Step 1: Press the key combination Alt + F11 to open the Microsoft Visual Basic for applications window .

Step 2: Select Insert , then select Module to enter the VBA code.

Step 3: Enter the following code into Module , then select Run to run the code.

Sub DeleteRows()
'Updateby20140314
Dim rng As Range
Dim InputRng As Range
Dim DeleteRng As Range
Dim DeleteStr As String
xTitleId = "Delete line"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng. Address, Type:=8)
DeleteStr = Application.InputBox("Delete Text", xTitleId, Type:=2)
For Each rng In InputRng
If rng.Value = DeleteStr Then
If DeleteRng Is Nothing Then
Set DeleteRng = rng
Else
Set DeleteRng = Application.Union(DeleteRng, rng)
End If
End If
Next
DeleteRng.EntireRow.Delete
End Sub

Step 4: When a dialog box appears, select the data area containing the line to delete into the input box. Then press OK .

Step 5: When the next dialog box appears, enter the condition value of the line you want to delete into the box. Then press OK to complete.

So you have completed deleting rows containing conditions in Excel using VBA code.

Above are 2 simple ways to help you delete conditional rows in Excel quickly and accurately. We invite you to read and apply. 

Isabella Humphrey

Update 04 April 2024