Instead of deleting rows one by one, it will be helpful if we can delete multiple rows at once. In this article, I will try to show you the process of how to delete multiple rows in Excel at once.
To make the explanation easier I’m going to use a sample dataset of a company named ABC. The dataset represents sales information for different products on different dates. The dataset has 4 columns these are Order ID, Product, Amount, and Date.
1. Using Context Menu to Delete Multiple Rows in Excel at Once
In order to delete multiple rows in a single command, the use of the Context menu is a very simple way. The steps are given below:
- Mark up the rows by dragging the mouse over the rows that we want to delete at once. Or you can hold CTRL and then select the rows you want to Delete.
- Right-click on the selection is necessary to initiate the Context menu.
- Then, click on Delete.
A dialog box of Delete will appear.
- Finally, we have to select the Entire row and click OK.
Then, we will get our desired output.
- Macro to Delete Row If Cell Contains Value
- Macro to Delete Row If Cell Contains in Excel
- VBA to Delete Every Other Row in Excel
- VBA to Delete Multiple Rows
- Delete Rows in a Range with VBA
- How to Delete Selected Rows
2. Deleting Multiple Rows at Once Through a Keyboard Shortcut
You can use a keyboard shortcut to delete multiple rows which is the quickest way we have seen.
The shortcut is CTRL + Minus(-).
- Select the required rows using the mouse at a stretch or separately with the CTRL key.
- Hit the CTRL + Minus(-) button.
- We will be able to see a dialogue box of delete.
- Select the Entire row and press OK.
Then, our desired output will come forward.
Read More: Delete All Rows Below a Certain Row in Excel
- Delete All Rows Not Containing Certain Text
- Delete Rows Based on Another List
- Remove Rows Containing Identical Transactions
- How to Delete Row Using VBA
- VBA to Delete Row If Cell Is Blank
- VBA to Delete Entire Row Based on Cell Value
3. Applying Conditional Formatting to Delete Multiple Rows
We can say that the use of Conditional Formatting is the coolest way to delete multiple rows at once. We can use Conditional Formatting to find out the rows according to the condition between the range from the dataset. Then, it will be easy to delete multiple rows at once.
- Select all the rows using the mouse. Here I selected the range B5 to E11.
- After that, open the Home tab >> from the Conditional Formatting >> select the New Rule option.
- Then, from the Select a Rule Type box, we have to select Use a formula to determine which cells to format.
- Input the following formula in the Format values where this formula is true. Here I used the formula:
=$D5 > 5000
Here, it will Highlight the values that are greater than 5000.
- Select Format.
A dialogue box named Format Cells will appear.
- We need to click on the Fill.
- Select a color of your choice. We selected Pink.
- Press the OK.
A New Formatting Rule box will appear again.
- Hit the OK button again.
Then, We will be able to see the colored rows according to the condition.
- Next, go to the Data option.
- We have to select Filter from the Sort & Filter.
We will be able to see the Filtered data.
- Go to the column according to the condition and select the Filter.
- Select Filter by Color.
- Afterward, select Filter by Cell Color and press OK.
We will be able to see the Colored rows only.
- Select the rows that you want to delete. I selected the range B5:E11.
- Right-click on the mouse and select Delete Row.
A warning message will appear.
- Press OK.
- Then, the selected row will be deleted and we have to click on the Filter icon again to remove Filter from the dataset.
We will see the output on the screen that we were looking for.
Related Content: How to Delete Rows in Excel with Specific Text
- How to Delete Filtered Rows
- How to Delete Unused Rows
- How to Find and Delete Row
- How to Delete Every Other Row
- How to Delete Blank Rows
- Delete Row If Cell Contains Specific Values
4. Using Excel VBA to Delete Multiple Rows at Once
We can use Visual Basic for Application (VBA) for the deletion of multiple rows at once.
- Go to the Developer tab and select Visual Basic.
We can also press Alt + F11 as an alternative way.
- From the Insert option, select Module.
- Write the following code in the Module.
Sub Delete_Multiple_Rows() Worksheets("VBA").Range("B6:E8").EntireRow.Delete End Sub
- Here, I’ve created a Sub procedure Delete_Multiple_Rows, then used the Worksheets object to mention my sheet name.
- Next, I used the Range.EntireRow property to select the Entire row then use the Delete method to delete multiple rows.
- Now, save the code.
- Then, press F5 or select Run Sub/UserForm (F5) to Run the code.
Code will be applied and we can see the results right in front of our eyes.
Related Content: How to Remove Highlighted Rows in Excel
- How to Delete Row If Cell Is Blank in Excel
- How to Delete Empty Rows at Bottom in Excel
- How to Delete Multiple Rows in Excel Using Formula
- How to Delete Multiple Rows in Excel with Condition
- How to Delete Rows in Excel without Affecting Formulas
- How to Delete Rows in Excel That Go on Forever
- How to Delete Infinite Rows in Excel
5. Applying Delete Command to Delete Multiple Rows at Once
We can use the Delete command from the ribbon as another way to delete multiple rows at once.
- Select the rows that need to be deleted by pressing the CTRL key and using the mouse simultaneously.
- Open the Home tab >> go to Cells >> from Delete >> select Delete Sheet Rows.
The selected rows will go away instantly.
Related Content: How to Delete Hidden Rows in Excel
Download Practice Workbook
I hope this will be effective for the users to delete multiple rows in Excel at once as there are many ways to do so. Anyone can choose any process according to their choice. For further queries, leave your thoughts in the comment section.
- [Fixed!] Not Enough Memory to Delete Rows Error in Excel
- VBA Code to Delete Rows Based on Multiple Cell Value
- Excel VBA to Delete Rows with Specific Data
- VBA to Delete Selected Rows with Excel VBA
- VBA to Delete Empty Rows
- Macro to Delete Rows Based on Criteria
- Filter and Delete Rows with VBA
- How to Delete Unfiltered Rows in Excel Using VBA
- How to Delete Hidden Rows in Excel VBA