In case you are looking for some of the easiest ways to delete everything below a certain row in Excel, then look no further. You are in the right place. This article gives you guidelines that help you delete unwanted rows and cells below a certain row.
1. Use delete sheet rows option method
This method helps you delete certain rows below a certain row. Use the steps below;
- Highlight the rows you wish to delete
- Open Home Tab
- Select the option ‘Cells’
- In the dropdown menu that appears, click on Delete> click Delete sheet Rows
The undesired rows that you had selected will be deleted.
2. Use the right click method to delete everything below row in Excel
Follow the easy steps below if you want to use your mouse to delete the unwanted rows below the selected one.
- Highlight the cells that you wish to delete
- Right-Click using the mouse
- Click on Delete option
- The delete Wizard appears. Choose on Entire row > Press OK
These steps remove rows below a certain row.
3. Use the name box method
Use the steps below;
- Click and select the Name box area
- Type in those rows that you want deleted for example, range 15: 20
- Right- Click using the mouse
- Select the delete option and choose Entire row then press OK
- Alternatively; Open the Home Tab then select Cells
- Click Delete> Delete sheet Rows
You have successfully deleted all the rows below the one which you selected.
4. Using shift, end and down arrow keys
1. Select the row below which you want to delete everything.
2. Press and hold the Shift key, and then press the End key followed by the Down arrow key. This should select all the rows below the current row.
3. Press the Ctrl key and the “-” (minus) key together to bring up the “Delete” dialog box.
4. In the “Delete” dialog box, select “Entire row” and click “OK” to delete all the selected rows.
Alternatively, you can also right-click on the row number of the first row you want to delete, select “Delete” from the context menu, and then select “Entire row” to delete all the rows below the current row.
5. Using ctrl + “-” shortcut keys
1. Go to the row below which you want to delete everything.
2. Click on the row number to select the entire row.
3. Press the Ctrl + Shift + End keys to select all the rows below the current row.
4. Press the Ctrl + “-“ (minus) keys to delete all the selected rows.
5. In the “Delete” dialog box, select “Entire row” and click “OK” to delete all the selected rows.
This method is similar to the previous methods, but it selects all the rows below the current row using the Ctrl + Shift + End shortcut and then deletes the selected rows using the Ctrl + “-” shortcut. This method is particularly useful if you have a large worksheet with many empty rows below the current row, as it selects all the rows in a single step.
6. Use VBA code to delete all rows below a certain row
You can easily delete rows from a specific point using VBA code . Follow these steps:
1. Press Alt+F11
2. Click insert > Module
3. (Copy and paste the code below (Replace data with the name of your sheet and 5 with the first row to delete)
Sub DeleteRowsBelow() Worksheets("data").Rows(5 & ":" & Worksheets("data").Rows.Count).Delete End Sub
4. Press F5 > Click Run
Similar Read: How to Get Past Excel Row Limit
7. How to delete all rows below the last active cell
Sometimes you may want to delete all rows below the last active ell to make your data sheet clean. The easiest way to do this is by hiding the rows.
Steps
Select the cell below the last active cell
Press ctrl + shift +down arrow (This will highlight all the cells below the last active cell)
Now press ctrl + shift +right arrow (This will highlight all the remaining unused cells)
Right click and select the hide option
Results
Use VBA to delete all rows below the last active cell
1. Press ALT + F11 to launch Microsoft VBA editor
2. Click on Insert
3. Select New Module
4. Copy and paste the VBA code below
Sub DeleteAllBelow() Rows(ActiveCell.Row & ":" & Rows.Count).Delete End Sub
Make sure to format the code as shown in the image above
5. Press F5
6. Click on Run
Conclusion
In this article, we have seen seven ways in which you can use to delete everything below row in Excel. All the methods are helpful depending on the size of your spreadsheet. If you have a small spreadsheet you can just highlight the rows then delete them. But if you have a long spreadsheet, its better to use shortcuts or the VBA code we have discussed in this article.