How to Delete Multiple Rows in Excel (3 Methods)

Maintaining a dataset is usual work in the corporate world. Deleting multiple rows is included in it and is required most frequently. This article will explain three different methods along with their sub-methods that will help you to delete multiple rows in Excel.

3 Methods of How to Delete Multiple Rows in Excel

Let a corporate company have a dataset of employee names, ID and contact numbers. You can notice the dataset has some blank rows and columns. We will be using the dataset to explain how to delete multiple rows in Excel.Sample dataset to explain the methods of how to delete rows in Excel

Download Practice Workbook

You can download the worksheet for practice from here.

1. Quick Methods to Delete Multiple Rows in Excel

Let us explore some of the quick methods to delete multiple rows in Excel.

a. Deleting Multiple Rows Using Right-Click Method (2 ways)

You can easily delete rows with the right-click method. Let’s divide it into two parts for the convenience of understanding.

I. Follow the steps below in case of selecting rows.

  • Select the unwanted rows by holding down CTRL. Let’s say we want to delete blank rows from the dataset.
  • Then, right-click on the rows and select Delete.

Selecting unwanted rows and right-click to delete the unwanted rows

The result is as below:

The result of selecting rows to delete them with right-click

II. In the case of selecting cells, you just must select any cell of the unwanted rows and right-click to select Delete.

Selecting cells and right-click to delete rows

Note: You must select the Entire row and click OK in the new box that will appear after selecting Delete.

Choosing Entire row from the Delete box

The result will be the same (I).

Result of deleting multiple rows by selecting relevant cells

Read more: How to Delete a Row If a Cell is Blank in Excel

b. Deleting Multiple Rows Using Keyboard Shortcut

Another quick and easy method of deleting multiple rows can be Keyboard Shortcut. Follow the picture to get the result.

Keyboard shortcut to select and delete rows

Result of using keyboard shortcut

c. Deleting Multiple Rows Using Delete from Home Tab

Delete from the Home tab can be another simple and fast method to delete multiple rows. Follow the steps below:

  • Select the unwanted rows. Here also let us select the blank rows which we want to delete from the database.

Selecting unwanted rows

  • Find Delete in the Cells option of the Home Tab.

Finding delete from the home tab

  • A drop-down menu will appear. From there select Delete Sheet Rows.

Selecting Delete Sheet rows from delete in home tab

Have a look at the result below.

Result of using delete from the home tab

The unwanted rows, that is the blank rows in this case, have been removed.

Read more: How to Use VBA to Delete Empty Rows in Excel

2. Using Editing Methods from Home Tab to Delete Multiple Rows in Excel

The Editing option in the home tab has some amazing options that will help you to delete multiple rows. To learn about them, go through the next two methods.

a. Using Go to Special from Find in Home Tab

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.

The steps are as follow:

  • Select the dataset. If your dataset is long, then press CTRL and select the start and end cells. This will help to select the whole dataset easily.

Selecting the whole dataset to apply Go to Special

  • Select Go to Special. You will find this in the Find & Select from the Editing option of the Home Tab.

Finding Go to Special from Find & Select

  • This will open a new box titled Go to Special. Select Blanks and Click on OK.

Choosing Blanks in Go to Special

You will notice that the blank cells are selected automatically.

Automatically selected blank cells

  • Repeat the method of finding Delete in 1(c) and you will get the result.

You can also follow the picture below.

Using delete to select delete sheet rows

Result of using Go To Special

Note: You will notice that the row having a blank in the cell is also removed since it is supposed to remove all rows with blanks in it.

b. Using Sort & Filter from Home Tab

We can use the Sort & Filter from the Home tab to delete multiple rows based on criteria. I will explain both the Sort and Filter options to delete multiple rows.

I. Let us start with the Sort. Let’s assume that we have another column with the dataset showing either the row is filled or blank.

Adding column filled or blank to the dataset

  • Follow the steps to get the result based on criteria.
  • Select the dataset.

Selecting the dataset along with the new column named filled or blank

  • Next, select Custom Sort from Sort & Filter. You can find Sort & Filter from the Home tab following the below picture.

Finding Custom Sort from the Sort& Filter of Home tab

  • A new box named Sort will come up. Choose the options as shown in the picture and then click OK.

Choosing options in the Sort box

You will see the Blank rows showing at the top of the dataset.

Blank rows showing at the top of the dataset

  • Delete the Blank rows using any of the quick methods in (1) and you will get the result with filled rows only.

Result of sorting blank rows and deleting them

II. Moving forward to Filter. Let us have a Helper Column with all the rows data using “&” between the cell addresses of a particular row.

Adding new column to the dataset to get all data of a row altogether

  • Using the Fill Handle, fill up the column.

Using Fill handle to fill up the new column

  • After that, selecting the column, choose Filter from Sort & Filter.

Choosing Filter from Sort & Filter

You will notice an arrow sign beside the Helper Column.

Arrow at the header of the helper column

  • Click on the arrow and tick only Blanks from the drop-down menu. Next, click OK.

Selecting blanks for filtering

Only the blank rows will be filtered out.

Showing blank rows after filtering

Delete the blank rows using Quick methods from (1).

Deleting blank rows

Notice that the rows are deleted. Since the rows were already blank you can be sure that the blank rows have been deleted as formatting gets deleted.

  • Again, click on the Filter sign shown in the header of Helper Column and then tick (Select All). After this click OK.

Selecting all after removing blank rows

The result is as follows:

Result after deleting blanks through filtering

Read more: How to Delete Specific Rows in Excel

3. Using VBA Macro to Delete Multiple 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.

a. Deleting Multiple Rows Using VBA Macro

Deleting multiple rows using VBA Macro requires the following steps.

  • Select the rows which you want to delete. Let us select rows 5,6 and 7 for this case.

Selecting rows for applying VBA Macro to delete multiple rows

  • Press ALT+F11 on your keyboard. This will open the Microsoft Visual Basic window.
  • Select your worksheet and then from Insert click on Module.

Opening new module for the selected sheet

  • A module will open named General. Copy and paste the below code.

Code:

Sub Delete_Rows()
 'delete multiple rows (rows 4, 5 and 6) 
Range("B4:B6").EntireRow.Delete 
End Sub

 

 

Code for deleting multiple rows using VBA Macro

Here, mention the number of the rows you want to delete in the parenthesis of the 2nd line of the code and give the range numbers of any of the cells of those rows.

  • Press F5 to run the code.
  • A new box will appear. Select the sheet name from there and click Run.

Selecting the sheet and running the VBA code to delete multiple rows

You will notice that the rows are deleted. The result is shown in the picture below.

Result of using VBA Macro

b. Deleting Alternate Rows Using VBA Macro

If you need to delete alternate rows using VBA Macro, you must follow the steps below.

  • Select the dataset.

Selecting the dataset to apply VBA Macro

  • Press ALT+F11 to go to the VBA window.
  • Select your sheet and then select Module from Insert.

Select the particular sheet and opening new module to paste code

  • A General window will open. Copy and paste the below code in the General window.

Code:

Sub Delete_Alternate_Rows_Excel()
	Dim SourceRange As Range
	Set SourceRange = Application.Selection
	Set SourceRange = Application.InputBox("Range:", "Select the range", SourceRange.Address, Type:=8)
	If SourceRange.Rows.Count >= 2 Then
    	Dim FirstCell As Range
    	Dim RowIndex As Integer
        Application.ScreenUpdating = False
    	For RowIndex = SourceRange.Rows.Count To 1 Step -2
        	Set FirstCell = SourceRange.Cells(RowIndex, 1)
            FirstCell.EntireRow.Delete
    	Next
        Application.ScreenUpdating = True
	End If
End Sub

Code of VBA Macro to delete alternate rows

  • To run the code press F5 on your keyboard.
  • A box will appear. Select your sheet name and click on Run.

Choosing the sheet where VBA Macro is to be applied

  • Another box will appear. Select the range of your dataset there and after selecting click on OK.

Selecting range of the dataset where VBA Macro will be applied

The result will look like this:

Result of VBA Macro to deleter alternate rows

Read more: VBA Macro to Delete Row if Cell Contains Value in Excel (2 Methods)

Things to Remember

Quick methods are easier but manual methods are also required sometimes. However, for a lengthy dataset, you can use any of the above methods which suit your requirements.

Conclusion

The article includes three different methods including some sub-methods for each to delete multiple rows in Excel. The methods start with quick and easy methods with just a right-click or using a keyboard shortcut or home tab. Moving forward with the Editing options from the Home tab and ends with using the  VBA Macro to delete multiple rows based on criteria. Hope this article helped you with deleting multiple rows in Excel.


Further Readings:


Syeda Fahima Nazreen

Hello People! This is Syeda Fahima Nazreen. I have completed my Bachelors in Science in Electrical and Electronic Engineering. I love to do research and work anything related to technology which includes research and development. I feel great to share my knowledge with you people and your thoughts and opinions about my writing is highly appreciated by me.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo