Instructions on 2 simple ways to delete conditional rows in Excel
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.
You should read it
- How to fix the SUM function doesn't add up in Excel
- Guidance on how to align Excel correctly
- How to keep Excel and Excel columns fixed?
- What is ### error in Excel? how to fix ### error in Excel
- How to display 0 in front of a number in Excel
- Instructions for searching and replacing in Excel tables
- MS Excel 2007 - Lesson 2: Customizing in Excel
- 3 ways down the line in Excel, line break, down row in 1 Excel cell
May be interested
- Complete tutorial of Excel 2016 (Part 6): Change the size of columns, rows and cellsby default the width of the column and the height of the row in excel may not match the data you entered. therefore, you want to change the width, height of rows and columns so that data is fully displayed on cells in excel.
- MS Excel 2007 - Lesson 10: Modify columns, rows and cellswhen you open a new blank workbook in excel 2007, cells, columns, and rows are set to default sizes. you can change the size of each cell, column and row, as well as insert new columns, rows and cells if needed.
- How to convert columns into rows and rows into columns in Excelyou need to convert excel columns into rows or convert data in excel rows into columns, if retraining takes a lot of time, just a trick that network administrator instructs below, you can turn the rows into columns or columns into rows extremely easy.
- MS Excel 2007 - Lesson 5: Edit Worksheetediting a spreadsheet in excel 2007 includes operations like: inserting cells, inserting rows / lines and inserting columns, deleting rows, deleting cells, deleting columns, finding data, replacing data or checking spelling, go to a certain cell in the spreadsheet.
- How to use Conditional Formatting to conditional formatting in Excelexcel has a condititional formatting tool that is quite adequate for the needs of users. however, the built-in formats of excel are not enough to meet your needs, excel supports users to easily create new rules.
- How to convert data from columns to rows in excel is extremely simpleis there a way to turn a column into a row instead of having to retype the data? very simple with the following 2 ways, tipsmake with you through the article below
- Ms Excel - Lesson 13: Use conditional formatting in Excelconditional formatting in excel allows you to apply different formatting formats, such as color formatting to one or more cells based on the data of those cells.
- MS Excel 2003 - Lesson 13: Using conditional formatting in Excelconditional formatting in excel allows you to apply different formats, such as color formatting, to one or more cells based on the data in those cells.
- How to use conditional formatting in Microsoft Excel 2016conditional formatting is a powerful tool and can change the 'appearance' of a cell based on its value, helping viewers to identify important data quickly. you can add colors, icons, data bars and color scales to the cell by creating conditional formatting rules.
- Excel - Convert columns to rows in Excelif you have an excel file and enter data in the wrong row or column or vice versa, what will you do? would you delete it and re-enter it? that takes up a lot of your time. let's see how taimienphi.vn guides you on how to convert columns into rows in excel.