How to Delete Multiple Rows in Excel at Once (5 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

Dataset of how to delete multiple rows in Excel at once


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:

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.

Use Context Menu to Delete Multiple Rows at Once

  • Right-click on the selection is necessary to initiate the Context menu.
  • Then, click on Delete.

Use Context Menu to Delete Multiple Rows at Once

A dialog box of Delete will appear.

  • Finally, we have to select the Entire row and click OK.

Use Context Menu to Delete Multiple Rows at Once

Then, we will get our desired output.

Use Context Menu to Delete Multiple Rows at Once


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(-).

Steps:

  • Select the required rows using the mouse at a stretch or separately with the CTRL key.

Use of Keyboard Shortcut to Delete Multiple Rows

  • Hit the  CTRL + Minus(-) button.
  • We will be able to see a dialogue box of delete.
  • Select the Entire row and press OK.

Use of Keyboard Shortcut to Delete Multiple Rows

Then, our desired output will come forward.

Use of Keyboard Shortcut to Delete Multiple Rows


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.

Steps:

  • Select all the rows using the mouse. Here I selected the range B5 to E11.

Apply Conditional Formatting to Delete Multiple Rows at Once

  • After that, open the Home tab >> from the Conditional Formatting >> select the New Rule option.

Apply Conditional Formatting to Delete Multiple Rows at Once

  • 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.

Apply Conditional Formatting to Delete Multiple Rows at OnceA 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.

Apply Conditional Formatting to Delete Multiple Rows at OnceA New Formatting Rule box will appear again.

  • Hit the OK button again.

Apply Conditional Formatting to Delete Multiple Rows at OnceThen, 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.

Apply Conditional Formatting to Delete Multiple Rows at Once

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.

Apply Conditional Formatting to Delete Multiple Rows at Once

We will be able to see the Colored rows only.

Apply Conditional Formatting to Delete Multiple Rows at Once

  • Select the rows that you want to delete. I selected the range B5:E11.
  • Right-click on the mouse and select Delete Row.

Apply Conditional Formatting to Delete Multiple Rows at Once

A warning message will appear.

  • Press OK.

Apply Conditional Formatting to Delete Multiple Rows at Once

  • Then, the selected row will be deleted and we have to click on the Filter icon again to remove Filter from the dataset.

Apply Conditional Formatting to Delete Multiple Rows at Once

We will see the output on the screen that we were looking for.

Apply Conditional Formatting to Delete Multiple Rows at Once


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.

Steps:

  • Go to the Developer tab and select Visual Basic.

We can also press  Alt + F11  as an alternative way.

Delete Multiple Rows Using VBA

  • From the Insert option, select Module.

Delete Multiple Rows Using VBA

  • Write the following code in the Module.
Sub Delete_Multiple_Rows()
Worksheets("VBA").Range("B6:E8").EntireRow.Delete
End Sub

Delete Multiple Rows Using VBA

  • 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.

Delete Multiple Rows Using VBA

Code will be applied and we can see the results right in front of our eyes.

Delete Multiple Rows Using VBA


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.

Steps:

  • Select the rows that need to be deleted by pressing the CTRL key and using the mouse simultaneously.

Apply Delete Command to Delete Multiple Rows at Once

  • Open the Home tab >> go to Cells >> from Delete >> select Delete Sheet Rows.

Apply Delete Command to Delete Multiple Rows at Once

The selected rows will go away instantly.

Apply Delete Command to Delete Multiple Rows at Once


Download Practice Workbook


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.


Related Articles


<< Go Back to Delete Rows | Rows in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Naimul Hasan Arif
Naimul Hasan Arif

Naimul Hasan Arif, a BUET graduate in Naval Architecture and Marine Engineering, has been contributing to the ExcelDemy project for nearly two years. Currently serving as an Excel and VBA Content Developer, Arif has written more than 120 articles and has also provided user support through comments His expertise lies in Microsoft Office Suite, VBA and he thrives on learning new aspects of data analysis. Arif's dedication to the ExcelDemy project is reflected in his consistent contributions and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo