How to delete rows in Excel using shortcuts or VBA macro

This article introduces various ways to delete rows based on cell values ​​in Excel. You will find hotkeys and Excel VBA codes in this article. You can delete rows automatically, or use standard lookup options in combination with useful shortcut keys.

Excel is an ideal tool for storing frequently changing data. However, updating a table after some changes can take a lot of time. Tasks can be as simple as removing all empty lines in Excel. Alternatively, you may need to find and delete duplicate data. One thing we are sure is that no matter the details are increased or decreased, you are always looking for the best solution to help you save time on your current job.

For example, you have a market where different suppliers sell their products. For some reason one of the vendors closed their business and now you need to delete all rows containing that vendor name, even if they are in a different column.

In this article, you will find Excel VBA codes and shortcuts that delete rows based on specific text or values. You will see how to easily find and select necessary information before deleting it. If your task is not deleting but adding rows, you can find out how to do it in "The Fastest Way to Insert Multiple Lines in Excel".

- Fastest Excel shortcut to delete rows in table - Delete rows from the entire table. - If there is data on the right side of the table, delete rows - Delete rows containing specific text in a single column - How to delete rows in Excel according to cell color - Delete rows containing specific text in different columns - Excel VBA macro to delete rows or delete every other row

The fastest Excel shortcut key to delete rows in tables ------------------------------------------------------------------------------------------------------------------

If you want to use the fastest way to delete multiple rows based on cell values, you need to select those rows correctly first.

To select a row, you can highlight adjacent cells with the desired value and press Shift Space, or select the desired non-adjacent cells while holding down the Ctrl key.

You can also use the Line Number button to select the entire row. You will see the highlighted number of rows next to the last button.

Once you have selected the necessary rows, you can quickly delete them using Excel's "Delete Rows" shortcut. Below you will find how to delete the selected row, whether you have a standard data table, or a table with data to the right of the table.

Delete rows from the entire table

If you have a simple Excel list without additional information on the right, you can use the Delete Line Shortcut key to delete the line in 2 simple steps:

1. Select the row you want to delete.
2. Press Ctrl - (minus sign on the primary keyboard) hotkey.

You will see the unused rows disappear immediately.

hint. You can highlight only the range containing the values ​​you want to delete. Then use the shortcut Ctrl - (minus sign on the primary keyboard) to get the standard Excel delete dialog box, allowing you to select the entire row radio button, or any other delete options you may need.

If there is data on the right side of the table, delete the row

Ctrl - (minus sign on the primary keyboard) Excel shortcut keys are the fastest way to delete rows. However, if you have any data on the right side of your main table, as shown in the screenshot below, it may delete the rows and take away the details you need to keep.

If this is the case, you need to format the data as an Excel table first.

1. Press Ctrl T, or go to the Start tab -> Format as table and select the style that suits you best.

   Format as table button " title="Go to Start tab -> Format as table button " width="451" height="186"> You will see the Create Table dialog box that you can use to highlight the necessary ranges.

   

2. Now that your list is formatted, select the range in the table that contains the values ​​or rows you want to delete.

   Notice. Make sure you do not use the row button to select the entire row.

3. Press Ctrl - (minus sign on the primary keyboard) to delete unwanted data in the table. The additional information on the right will remain the same.

Hope you find this "Delete Line" shortcut key helpful. Continue reading to find the Excel VBA code used to delete rows and learn how to eliminate data based on specific cell text.

Delete rows containing specific text in a single column

If the items of the row you are deleting appear only in one column, the following steps will guide you how to delete rows containing such values.

1. First, you need to apply filtering to the table. To do this, navigate to Excel's Data tab and click the Filter icon.
2. Filter columns containing deleted values ​​as needed. Click the arrow icon next to the column containing the desired item. Then uncheck the Select All option and select the check box next to the correct value. If the list is long, just enter the necessary text in the search field. Then click OK to confirm.
3. Select the filtered cell in the row you want to delete. There is no need to select the entire row.
4. Right-click the highlighted range and select the Delete Row option from the menu list.

Finally, click the filter icon again to clear it and you will see the rows containing these values ​​disappear from your table.

How to delete rows in Excel based on cell color

The filter option allows the data to be sorted by the color of the cell. You can use it to delete all rows containing specific background colors.

1. Apply filters to the table. Go to Excel's Data tab and click the Filter icon.
2. Click the arrow next to the desired column name to enter Filter by color and select the correct cell color. Click OK and you will see all the highlighted cells appear at the top.
3. Select the filtered colored cells, right-click them and select the Delete Row option from the menu.

That's it! Rows containing cells of the same color are deleted immediately.

Delete rows containing specific text in different columns

If the values ​​you want to delete are scattered in different columns, sorting can complicate the task. Below you will find a useful trick to delete rows based on cells containing specific values ​​or text. From my table below, I want to delete all rows containing January and it appears in 2 columns.

1. First search and select the cells containing the desired value by using the Find and Replace dialog box. Click Ctrl F to run it. hint. You can find the same dialog box in the Start tab -> Find and Select and select the Find option from the drop-down list.

   

   Find and select and select the search option " title="Go to the Start tab -> Find and select and select and select the search option " width="319" height="173">

2. Enter the desired value in the Find Content field and select any necessary additional options. Then press Find all to view the results.

3. The results will appear in the Find and Replace window.

   Press and hold the Ctrl key in the window to select the found value. You will automatically highlight the found values ​​in the table.

   

4. Now navigate to the Start tab -> Delete -> Delete sheet rows .

   Delete -> Delete Worksheet Rows" title="Navigate to Start Tab-> Delete -> Delete Worksheet Rows" width="324" height="179"> Prompt. You can delete rows containing selected values ​​by pressing Ctrl - (minus sign on the motherboard) and selecting the radio button to complete row .

Look! Unwanted lines have been deleted.

Excel VBA macros to delete rows or delete every other row

If you are always looking for solutions that automate this or that Excel routine, use the macro below to simplify your delete row task. In this section you will find 2 VBA macros that will help you delete rows containing selected cells or delete every other row in Excel according to your settings.

The macro RemoveRowsWithSelectedCells will delete all rows containing at least one highlighted cell.

The macro RemoveEveryOtherRow , as the name implies, will help you remove every other row/third row, etc. according to your settings. It will delete rows from the current mouse cursor position until the table ends.

If you don't know how to insert macros, feel free to check out How to Insert and Run VBA Code in Excel.

Sub RemoveRowsWithSelectedCells() Dim rngCurCell, rng2Delete As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For Each rngCurCell In Selection If Not rng2Delete Is Nothing Then Set rng2Delete = Application.Union(rng2Delete, _ ActiveSheet.Cells(rngCurCell.Row, 1)) Else Set rng2Delete = rngCurCell End If Next rngCurCell If Not rng2Delete Is Nothing Then rng2Delete.EntireRow.Delete End If Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub Sub RemoveEveryOtherRow() Dim rowNo, rowStart, rowFinish, rowStep As Long Dim rng2Delete As Range rowStep = 2 rowStart = Application.Selection.Cells(1, 1).Row rowFinish = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For rowNo = rowStart To rowFinish Step rowStep If Not rng2Delete Is Nothing Then Set rng2Delete = Application.Union(rng2Delete, _ ActiveSheet.Cells(rowNo, 1)) Else Set rng2Delete = ActiveSheet.Cells(rowNo, 1) End If Next If Not rng2Delete Is Nothing Then rng2Delete.EntireRow.Delete ' Hide every other line'rng2Delete.EntireRow.Hidden = True End If Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub Tip. If your task is to color every other row/third row, etc. with different colors, you will find the steps in Alternate Row Color and Column Coloring in Excel (Ranning Rows and Columns).

In this article, I describe how to delete rows in Excel. Now you have several useful VBA macros to delete selected rows, you know how to delete every other row and how to use find and replace to help you search and select all rows containing the same value, and then eliminate them. Hope the above suggestions simplify your work in Excel and give you more free time to enjoy this final summer day. I wish you happiness and excellence in Excel!

The above is the detailed content of How to delete rows in Excel using shortcuts or VBA macro. For more information, please follow other related articles on the PHP Chinese website!