How to Delete All Rows Below a Certain Row in Excel (6 Ways)

If you are looking for some of the easiest ways to delete all rows below a certain row in Excel, then you are in the right place. By following the methods in this article, you will be able to delete your undesired rows below a certain row easily and effectively rather than doing it manually.

Download Workbook

6 Ways to Delete All Rows below a Certain Row in Excel

I have used the following data table of “XYZ Company” with which I will explain the easiest methods to delete all rows below a certain row in Excel. For this purpose I have used Microsoft Excel 365 version, you can use any other version according to your convenience.

delete all rows below a certain row

Method-1: Using Delete Sheet Rows Option 

Let’s say, you want to delete the last three rows that mean Row 11 to Row 13  for Pant as a product. You can do this by using the Delete Sheet Rows Option.

Step-01:
➤Select the Cell B11 of Row 11
➤Press CTRL+SHIFT+ +

using delete sheet rows

Then, all of the cells of the last three rows will be selected.

using delete sheet rows

Step-02:
➤Go to Home Tab>>Cells Dropdown>>Delete Dropdown>>Delete Sheet Rows Option

using delete sheet rows

Result:
In this way, all of the unwanted rows below a certain will be deleted.

delete all rows below a certain row

Read More: How to Delete Rows in Excel: 7 Methods

Method-2: With a Mouse Click Deleting All Rows below a Certain Row

If you want to delete the rows below the row for Jacket 3 then you can do it just with a mouse click.

delete all rows below a certain row

Step-01:
➤Follow Step-01 of Method-1
Then the unwanted rows which you want to delete will be selected.

with a mouse click

Step-02:
➤Right-click on your mouse
➤Select Delete Option

with a mouse click

Then, the Delete Wizard will appear.
➤Select Entire row Option and Press OK

with a mouse click

Result:
Afterward, you will be able to remove the rows below a certain row for Jacket 3.

delete all rows below a certain row

Read More: How to Delete Multiple Rows in Excel with Condition (3 Ways)

Method-3: Using Name Box

You can use the Name Box to delete all of the rows below a certain row like a row for Jacket 3.

delete all rows below a certain row

Step-01:
➤Select the Name Box area.

Name Box

➤Type the range of rows you want to delete.
In this case, the range is 11:13

Name Box

After that, you will be able to select the undesired rows automatically.

Name Box

➤Follow Step-2 of Method-1 or Method-2

Result:
In this way, you will be able to delete all of the rows below the row for Jacket 3

delete all rows below a certain row

Read More: How to Use Macro to Delete Rows Based on Criteria in Excel (3 Ways)


Similar Readings:


Method-4: Using VBA Code to Delete All Rows below a Certain Row

You can use a VBA code to delete all rows below a certain row like here I will delete the last three rows.

delete all rows below a certain row

Step-01:
➤Go to Developer Tab>>Visual Basic Option

VBA code

Then, Visual Basic Editor will open up.
➤Go to Insert Tab>>Module Option

VBA code

After that, Module1 will be created.

VBA code

Step-02:
➤Now, Write the following code here.

Sub RemoveRowsBelow()

'delete all rows below certain row

Worksheets("VBA").Rows(11 & ":" & Worksheets("VBA").Rows.Count).Delete

End Sub

Here, VBA is the sheet name and 11 refers to from which row you want to delete the rest of the rows.

VBA code

➤Press F5

Result:
Then, you will get the following table where you will be able to remove the undesired rows.

delete all rows below a certain row

Read More: How to Delete Selected Rows in Excel(8 Approaches)

Method-5: Deleting All Rows below the Last Active Row

Assume, you want to delete all of the empty rows below the data table. You can do it by hiding the empty rows easily.

delete all rows below a certain row

Step-01:
➤Select the cell from where you want to remove the rows.

delete all rows

➤Press CTRL+SHIFT+

delete all rows

➤Press CTRL+SHIFT+

delete all rows

In this way, all of the unused cells will be selected.
➤Right-click on your mouse
➤Select Hide Option

delete all rows

Result:
Then, You will be able to hide all of the rows below the data table as follows.

delete all rows below a certain row

Read More: Excel Shortcut to Delete Rows (With Bonus Techniques)

Method-6: Deleting All Rows below the Last Active Row with a VBA Code

If you want to delete the rows below the last active row including the active row you can use a VBA code. Let’s say, here our last active row is the row for Pant 1 and we will remove the following rows including this active row.

delete all rows below a certain row

Step-01:
➤Follow Step-01 of Method-4
➤Now, write the following code.

Sub Rmvall()

    Rows(ActiveCell.Row & ":" & Rows.Count).Delete

End Sub

 ActiveCell.Row will return the row number of active row and Rows.Count will count the rows in Excel and return the bottom-most row number and these two numbers will be the range for ROWS 

Finally, these rows will be deleted.

VBA code

Step-02:
➤Select the row from where you want to delete the rows
➤Go to Developer Tab>>Macros Option

VBA code

Then Macro Wizard will pop up
➤Select  Rmvall as Macro name (the name used for VBA code)
➤Press Run

VBA code

Result:
Then, you will get the following result

delete all rows below a certain row

Read More: How to Delete Row Using VBA (14 Ways)

Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

Practice

Conclusion

In this article, I tried to cover the easiest ways to delete all rows below a certain row effectively. Hope you will find it useful. If you have any suggestions or questions feel free to share them with us.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo