How To Delete All Rows Below Certain Row Or Active Cell In Excel ?

In order to successfully organise enormous datasets in Microsoft Excel, rows and columns must frequently be changed. It could occasionally be required to remove all the rows that are beneath the active cell or a particular row. Whether you're trying to streamline your spreadsheet, get rid of unused rows, or organise your data, eliminating rows can greatly enhance data organisation and analysis.

This article will show you how to delete every row in Excel that is beneath a certain row or the currently selected cell. You will discover many approaches to complete this work that take into account user preferences and skill levels. No matter how much Excel expertise you have, following these detailed steps will enable you to confidently eliminate unnecessary rows while preserving the accuracy and conciseness of your data.

Delete All Rows Below Certain Row Or Active Cell In Excel

Here we will first create a VBA module and run it to complete the task. So let us see a simple process to know how you can delete all rows below a certain row or active cell in Excel.

Step 1

Consider an Excel sheet where the data in the sheet is similar to the below image.

First, right−click on the sheet name and select View Code to open the VBA application. Then, click on Insert, select Module, and copy the below code into the textbox as shown in the below image.

Right click > View code > Insert > Module > Copy.

Code

Sub DeleteRowsBelow()
   Worksheets("Sheet1").Rows(5 & ":" & Worksheets("Sheet1").Rows.Count).Delete
End Sub

Step 2

Then save the sheet as a macro−enabled template and click F5 to complete the task.

Save > F5.

Note

If you want to delete rows below active cells, use the below code.

Code

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

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can delete all rows below a certain row or active cell in Excel to highlight a particular set of data.

Pradeep Kumar

e

Updated on: 07-Jul-2023

438 Views

Kickstart Your Career

Get certified by completing the course

Get Started