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.
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.
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.
➤Select the Cell B11 of Row 11
➤Press CTRL+SHIFT+ ➜ + ⬇
Then, all of the cells of the last three rows will be selected.
➤Go to Home Tab>>Cells Dropdown>>Delete Dropdown>>Delete Sheet Rows Option
In this way, all of the unwanted rows below a certain will be deleted.
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.
➤Follow Step-01 of Method-1
Then the unwanted rows which you want to delete will be selected.
➤Right-click on your mouse
➤Select Delete Option
Then, the Delete Wizard will appear.
➤Select Entire row Option and Press OK
Afterward, you will be able to remove the rows below a certain row for Jacket 3.
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.
➤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
In this way, you will be able to delete all of the rows below the row for Jacket 3
- 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: 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.
➤Go to Developer Tab>>Visual Basic Option
Then, Visual Basic Editor will open up.
➤Go to Insert Tab>>Module Option
After that, Module1 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.
Then, you will get the following table where you will be able to remove the undesired rows.
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.
➤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
Then, You will be able to hide all of the rows below the data table as follows.
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.
➤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.
➤Select the row from where you want to delete the rows
➤Go to Developer Tab>>Macros Option
Then Macro Wizard will pop up
➤Select Rmvall as Macro name (the name used for VBA code)
Then, you will get the following result
Read More: How to Delete Row Using VBA (14 Ways)
For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.
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.