HomeApplicationOffice informationInstructions on 2 simple ways to delete conditional rows in

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. 

4 ★ | 2 Vote
Excel

You should read it

May be interested

  • Tell you how to turn off security warnings in Excel in the simplest and most detailed wayPhoto of Tell you how to turn off security warnings in Excel in the simplest and most detailed way
    protected view is an extremely useful feature in microsoft software, but not everyone knows how to use it. this article will guide you to turn off security warnings in excel quickly and simply.
  • How to delete PDF pages extremely quicklyPhoto of How to delete PDF pages extremely quickly
    in a soft document file, sometimes there are pages that you want to hide or delete. pdf is no exception. however, deleting pdf pages is not like other common document file types.
  • How to view multiple pages in Word at the same timePhoto of How to view multiple pages in Word at the same time
    to easily view the content between multiple word document pages, instead of having to scroll through each page or continuously click to zoom in and out of word documents, users should use multiple pages mode to view multiple word pages at the same time. at the time.
  • How to change worksheet size in ExcelPhoto of How to change worksheet size in Excel
    excel has settings for you to resize the spreadsheet, adjusting it to display all columns and rows on the same page. or if needed, users can also adjust the number of printed pages displayed for preview before printing.
  • How to Copy Uncopyable Text ContentPhoto of How to Copy Uncopyable Text Content
    do you want to copy text content on a web page that prevents selecting or right-clicking on the content? although it may seem impossible, there are ways you can copy text content on a protected website, even if you can't right-click on the page. this article shows simple ways to copy any content on chrome, safari, firefox and microsoft edge when using a computer, phone or tablet.
  • How to align in Word extremely accuratelyPhoto of How to align in Word extremely accurately
    alignment in word has standard regulations issued so that agencies, facilities, and schools can align word margins properly.

Office information