How to Delete All Rows Below a Certain Row in Excel?

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 this article, you will be able to learn 6 easy ways to delete all rows below a certain row easily and effectively rather than doing it manually in Excel.

In the following image, we have used the delete sheet rows option to erase rows below a certain one in Excel.

How to Delete All Rows Below a Certain Row in Excel: 6 Easy Ways

Here, 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. However, it contains the details of some products. For this purpose, I have used Microsoft Excel 365 version, you can use any other version according to your convenience.

Method-1: Use of Delete Option in Context Menu to Delete Rows

Let’s say, you want to delete the last three rows which means Row 11 to Row 13  for Pant as a product. You can select the rows and delete them from the Context menu. Hence, follow the steps below.

Steps:

• Select the Cell B11.
• Press CTRL+SHIFT+➜+⬇.
• Then, all of the cells of the last three rows will be selected.

• After that, right-click and select the Delete option from the Context Menu.

• Now, select Entire row and press OK.

• Finally, all of the unwanted rows below a certain will be deleted.

Method-2: Delete Sheet Rows Option to Delete Rows Below a Certain Row

Additionally, if you want to delete the rows below the row for Jacket 3 then you can do it just with the Delete Sheet Rows option. Hence, follow the steps below.

Steps:

• Similarly, select the 3 rows with the help of the mouse.
• Then, go to the Home Tab.
• Next, from the Cells dropdown, select Delete dropdown.
• Afterwards, click on the Delete Sheet Rows option.

• Finally, you will be able to remove the rows below a certain row for Jacket 3.

Method-3: Utilize Name Box in Excel to Delete Rows

Furthermore, you can use the Name Box to delete all of the rows below a certain row like a row for Jacket 3. Hence, follow the steps below.

Steps:

• Select the Name Box area.
• Type the range of rows you want to delete. In this case, the range is 11:13.

• After that, you will be able to select the undesired rows automatically.
• Follow the second step of Method-1 or Method-2.
• Finally, you will be able to delete all of the rows below the row for Jacket 3.

Method-4: Apply Excel VBA Code to Remove Rows

However, you can use VBA to delete rows below a certain row here I will delete the last three rows. Hence, follow the steps below.

Steps:

• Go to Developer Tab>>Visual Basic Option.
• Then, Visual Basic Editor will open up.
• Go to Insert Tab>>Module Option.
• After that, Module 1 will be created.

• 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.
• Press F5.
• Then, you will get the following table where you will be able to remove the undesired rows.

Method-5: Delete All Rows Below the Last Active Row by Keyboard Shortcuts

Using keyboard shortcuts to delete rows is another way for you.

For instance, you want to delete all of the empty rows below the data table. You can do it by hiding the empty rows easily. Hence, follow the steps below.

Steps:

• Select the cell from where you want to remove the rows.

• Press CTRL+SHIFT+⬇.
• Press CTRL+SHIFT+➜.

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

• Lastly, you will be able to hide all of the rows below the data table as follows.

Method-6: Remove Rows Below the Last Active Row with Excel VBA

Lastly, 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. Hence, follow the steps below.

Steps:

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

Sub Rmvall()
    Rows(ActiveCell.Row & ":" & Rows.Count).Delete
End Sub

• Here, ActiveCell.Row will return the row number of active rows 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.
• After that, select the row from where you want to delete rows.

• Go to the Macros Option.
• Now, select  Rmvall as the Macro name (the name used for the VBA code).
• Press Run.

• Lastly, you will get the following result.

Download Workbook

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.

You May Also Like to Read

• How to Delete All Rows Not Containing Certain Text in Excel
• How to Delete Rows Based on Another List in Excel
• How to Remove Highlighted Rows in Excel
• Delete Row If Cell Contains Specific Value
• Delete Row If Cell Is Blank
  
• Delete Rows with Specific Text
• How to Delete Blank Rows
• How to Delete Empty Rows at the Bottom

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