Syeda Fahima Nazreen
Maintaining a dataset is regular work in the corporate world. Deleting multiple rows is included in it and is required most frequently. This article will explain 8 handy methods along with their sub-methods on how to delete multiple rows in Excel.
Download Practice Workbook
You can download the worksheet for practice from here.
8 Handy Methods to Delete Multiple Rows in Excel
Let’s assume a corporate company has a dataset of employee Names, IDs and Contact Numbers. You can notice the dataset has some blank rows.
We will follow the methods below to delete multiple blank rows from this dataset.
1. Delete Multiple Rows from Context Menu in Excel
Deleting rows from the Context Menu is one of the quickest methods. Let’s see how it works.
- First, select the blank rows by holding the Ctrl button on your keyboard.
- Then, right-click on the rows and select Delete from its Context Menu.
- As a result, you will get the following output.
- In the case of selecting cells, just select the blank cells by pressing the Ctrl button and right-click to select Delete.
- Then, select the Entire row option and click OK in the Delete window.
- Finally, you will get the same result as before.
Read More: How to Delete Empty Rows at Bottom in Excel (7 Ways)
2. Use Keyboard Shortcuts to Remove Multiple Rows
Another quick and easy method of deleting multiple rows can be Keyboard Shortcut. Follow the steps below for this.
- First, select the first blank row from the dataset.
- Then, press Ctrl > Minus (–) button on your keyboard.
- Therefore, it will immediately remove the selected blank row and shift up the rest of the rows.
- Next, follow the same procedure for each blank row and finally the output will look like this.
Read More: How to Delete Unused Rows in Excel (8 Easy Ways)
3. Delete Multiple Rows Directly from Home Tab in Excel
Delete from the Home tab can be another simple and fast method to delete multiple rows. Follow the steps below.
- First, select the blank rows by holding the Ctrl button on your keyboard.
- Then, select Home > Cells > Delete.
- Afterward, a drop-down menu will appear.
- Here, select Delete Sheet Rows.
- Finally, we have successfully removed the unwanted rows.
Read More: How to Delete Multiple Rows in Excel Using Formula (5 Methods)
4. Apply Go To Special Feature to Remove Multiple Rows
Go to Special is one of the methods which helps to select criteria-based data from the database. For our dataset let’s stick with finding blanks and deleting the blank rows. For this, The steps are as follows.
- In the beginning, select the whole dataset.
- Then, click on Find & Select from the Home tab.
- Afterward, choose Go To Special from its drop-down menu.
- As a result, this will open a new box titled Go to Special.
- Here, select Blanks and click on OK.
- Accordingly, you will notice that the blank cells are selected automatically.
- Now, delete them following any of the methods above and the final result will look like this.
Read More: How to Remove Empty Rows in Excel (11 Easy Ways)
5. Eliminate Multiple Rows with Sort Tool in Excel
The Sort Tool is a powerful one to delete multiple rows in Excel. To do the task, follow the steps below.
- In the beginning, insert a new column with the dataset showing either the row is Filled or Blank.
- Then, select the whole dataset.
- Next, click on Home > Editing > Sort & Filter.
- Here, choose Custom Sort from the drop-down menu.
- Accordingly, a new dialogue box named Sort will come up.
- In this box, choose the options as shown in the picture and then click OK.
- As a result, you will see the Blank rows showing at the top of the dataset.
- Lastly, delete the Blank rows using any of the above methods and you will get the result with Filled rows only.
Read More: How to Delete Multiple Rows in Excel at Once (5 Methods)
Similar Readings
- Excel Delete Rows in a Range with VBA (3 Easy Ways)
- Delete an Excel Row If a Cell Contains Specific Values (3 Methods)
- Macro to Delete Row in Excel If Cell is Blank
- How to Delete Filtered Rows in Excel (5 Methods)
- Excel Shortcut to Delete Rows (With Bonus Techniques)
6. Use Filter Command to Delete Multiple Rows
The Filter command is also very helpful to remove multiple rows. Let’s follow the steps below to do this.
- First, create a Helper Column along with the dataset.
- Then, insert this formula in Cell E5 to combine the texts of the Cell range B5:D5.
- After this, use the Fill Handle and fill up the column.
- Next, selecting the column, choose Filter from Sort & Filter.
- Following this, you will notice an arrow sign beside the Helper Column.
- Then, click on the arrow and tick only Blanks from the list.
- Then, click on OK to get only the blank rows like this.
- Now, delete the blank rows.
- Again, click on the Filter sign shown in the header of the Helper Column and then tick (Select All).
- After this, click OK.
- Finally, you will get the following result.
Read More: How to Delete Multiple Rows with VBA in Excel (4 Ways)
7. Insert Excel Formulas to Delete Multiple Rows
We can also apply an Excel formula to delete multiple rows. In this case, we will use the IF function to generate a formula and delete the rows. Let’s see how it works.
- First, create a Helper Column along with the dataset.
- Then, insert this formula in Cell E5.
=IF(C5>=150,"Yes","No")
- Then, use the Fill Handle command to get the output all over the Helper Column.
- Lastly, delete the rows that have No as output and you will get the following result.
Read More: VBA Macro to Delete Row if Cell Contains Value in Excel (2 Methods)
8. Apply VBA to Remove Rows in Excel
You might like to use code while working with Excel, to automate the entire process. You can also do that using VBA Macro in Excel. We can delete both multiple consecutive and alternative rows with VBA.
8.1. Remove Multiple Consecutive Rows
Let’s follow the steps below to delete multiple consecutive rows.
- In the beginning, select the rows which you want to delete.
- For instance, let us select rows 8,9 and 10 for this case.
- Then, press Alt + F11 on your keyboard to open the Microsoft Visual Basic for Applications window.
- Here, choose Module from the Insert tab.
- Next, insert this code on the Code window.
Sub Dlt_Rows()
Range("B8:B10").EntireRow.Delete
End Sub
- Lastly, press F5 on your keyboard and it will instantly remove the selected rows.
8.2. Delete Multiple Alternate Rows
If you need to delete alternate rows using VBA Macro, you must follow the steps below.
- First, select the whole dataset.
- Then like before, open Module and insert this code.
Public Sub Delete_All_Blank_Rows()
Dim SourceRng As Range
Dim EntireRow As Range
Set SourceRng = Application.Selection
If Not (SourceRng Is Nothing) Then
Application.ScreenUpdating = False
For I = SourceRng.Rows.Count To 1 Step -1
Set EntireRow = SourceRng.Cells(I, 1).EntireRow
If Application.WorksheetFunction.CountA(EntireRow) = 0 Then
EntireRow.Delete
End If
Next
Application.ScreenUpdating = True
End If
End Sub
- Next, press Ctrl + S to save the code and then close the window.
- Then, select Macros from the Developer tab.
- Following this, choose Delete_All_Blank_Rows from the Macro name list in the Macros window and click on Run.
- Finally, you have successfully deleted multiple blank rows all at once.
Read More: Excel VBA: Delete Row If Cell Is Blank (A Complete Guide)
Conclusion
The article includes 8 handy methods on how to delete multiple rows in Excel. Hope this article helped you enormously. Follow ExcelDemy for more tutorials like this.
Related Articles
- How to Use Macro to Delete Rows Based on Criteria in Excel (3 Ways)
- Delete Every Other Row In Excel (4 Methods)
- How to Delete Infinite Rows in Excel (5 Easy Ways)
- Delete Rows in Excel That Go on Forever (4 Easy Ways)
- How to Delete Every nth Row in Excel (Easiest 6 Ways)
- Delete Multiple Rows in Excel with VBA (4 Ways)
- How to Delete Rows Based on Another List in Excel (5 Methods)
