Naimul Hasan Arif
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 of different products on different dates. The dataset has 4 columns these are Order ID, Product, Amount, and Date.
Download Practice Workbook
5 Methods to Delete Multiple Rows in Excel at Once
1. Use Context Menu to Delete Multiple Rows 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:
Steps:
- 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.
Read More: How to Delete Multiple Rows in Excel Using Formula (5 Methods)
2. Use of Keyboard Shortcut to Delete Multiple Rows
The quickest way to delete multiple rows is to use the keyword shortcut. You can use the CTRL + Minus(-)Â keys from the keyboard.
Steps:
- Select the required rows using the mouse at a stretch or separately with the CTRL Â key.
- Hit the CTRL + Minus(-) buttons.
- 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: Excel Shortcut to Delete Rows (With Bonus Techniques)
3. Apply Conditional Formatting to Delete Multiple Rows at Once
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.
Steps:
- Select all the rows using the mouse. Here I selected the range B5 to E11.
- After that, open Home tab >> from the Conditional Formatting >> select New Rule
- 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 > 5000Here, 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 the 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.
Read More: How to Delete Multiple Rows in Excel with Condition (3 Ways)
Similar Readings:
- Delete Row If Cell is Blank in Excel (4 Methods)
- How to Delete Every nth Row in Excel (Easiest 6 Ways)
- Use VBA to Delete Empty Rows in Excel
- How to Filter and Delete Rows with VBA in Excel (2 Methods)
- Delete Selected Rows with Excel VBA (A Step-by-Step Guideline)
4. Delete Multiple Rows Using VBA
We can use Visual Basic for Application (VBA) for the deletion of multiple rows at once.
Steps:
- 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, used the Range.EntireRow property to select the Entire row then used 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.
Read More: How to Delete Multiple Rows in Excel (3 Methods)
5. Apply 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.
Steps:
- 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 Specific Rows in Excel (8 Quick Ways)
Practice Section
I’ve been given the practice to practice the explained methods.
Conclusion
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.
