Guide on 2 ways to delete conditional rows in Excel
Guide on 2 ways to delete conditional rows in Excel: If you want to delete one or more rows (based on certain conditions) in Excel, you may have to spend a lot of time selecting and deleting in the data table. This method is also prone to errors, especially for spreadsheets with large amounts of data. Buffcom.net article will guide you on 2 simple and quick ways to delete conditional rows in Excel. We invite you to read the article.
Method 1: Using Find and Replace
In addition to the search and replace function, Find and Replace feature can also be used to delete conditional rows in Excel. Here are the steps:
Step 1: Select the data range that contains the rows you want to delete.
Step 2: Press the Ctrl + F key combination to open the Find and Replace dialog box.
Step 3: In the Find and Replace dialog box, enter the value of the conditional cell that you want to delete in the Find what box, then select Find All. In this example, we will delete cells that have the value “sữa béo”.
Guide on 2 ways to delete conditional rows in Excel
Step 4: Press the Ctrl + A key combination to select all search results at the bottom of the Find and Replace dialog box. Then close the dialog box.
Guide on 2 ways to delete conditional rows in Excel
Step 5: Right-click on one of the selected cells, then select Delete, and then select Table Rows.
Guide on 2 ways to delete conditional rows in Excel
Thus, with just a few simple steps, rows that contain the value “sữa béo” have been deleted from the data table.
Guide on 2 ways to delete conditional rows in Excel
Method 2: Using VBA code
In addition to the Find and Replace function, you can also delete conditional rows using VBA code. Here are the steps:
Step 1: Press the Alt + F11 key combination to open the Microsoft Visual Basic for Applications window.
Guide on 2 ways to delete conditional rows in Excel
Step 2: Select Insert, and then select Module to enter the VBA code.
Guide on 2 ways to delete conditional rows in Excel
Step 3: Enter the following code into the module, and then select Run to execute the code.
Sub DeleteRows()
'Updateby20140314
Dim rng As Range
Dim InputRng As Range
Dim DeleteRng As Range
Dim DeleteStr As String
xTitleId = "Xoa dong"
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
Guide on 2 ways to delete conditional rows in Excel
Step 4: When the dialog box appears, select the data range that contains the row you want to delete and input it into the text box. Then press OK.
Guide on 2 ways to delete conditional rows in Excel
Step 5: When the next dialog box appears, enter the condition value of the row you want to delete into the text box. Then press OK to complete.
Guide on 2 ways to delete conditional rows in Excel
By following these steps, you have completed deleting the rows that meet the condition in Excel using VBA code.
These are 2 simple ways to quickly and accurately delete rows that meet certain conditions in Excel. We hope this helps and wish you all the best in your work.
