Tanjima Hossain
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.
Download Workbook
6 Easy Ways to Delete All Rows Below a Certain Row in Excel
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 Erase Rows
Let’s say, you want to delete the last three rows which means Row 11 to Row 13 for Pant as a product. However, you can do this by using the Delete Option. 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.
Read More: How to Delete Rows in Excel: 7 Methods
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 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.
Read More: How to Delete Multiple Rows in Excel with Condition (3 Ways)
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 Step-2 of Method-1 or Method-2.
- Finally, you will be able to delete all of the rows below the row for Jacket 3.
Read More: How to Use Macro to Delete Rows Based on Criteria in Excel (3 Ways)
Similar Readings:
- How to Delete Rows Based on Another List in Excel (5 Methods)
- Excel VBA: Delete Row If Cell Is Blank (A Complete Guide)
- VBA to Delete Every Other Row in Excel (6 Criteria)
- How to Delete Multiple Rows in Excel at Once (5 Methods)
- Delete Hidden Rows in Excel VBA (A Detailed Analysis)
Method-4: Apply Excel VBA Code to Remove Rows
However, you can use a VBA code to delete all 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.
Read More: How to Delete Selected Rows in Excel(8 Approaches)
Method-5: Delete All Rows Below Last Active Row by Keyboard Shortcuts
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.
Read More: Excel Shortcut to Delete Rows (With Bonus Techniques)
Method-6: Remove Rows Below 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 Macros Option.
- Now, select Rmvall as Macro name (the name used for VBA code).
- Press Run.
- Lastly, you will get the following result.
Read More: How to Delete Row Using VBA (14 Ways)
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.
