Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

Delete All Rows Below a Certain Row in Excel (6 Easy Ways)

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.

Delete Sheet Rows Option for Deleting All Rows Below a Certain Row


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.

Excel Delete All Rows Below a Certain Row Dataset


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.

Use of Delete Option to Delete All Rows Below a Certain Row

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

Delete option in excel

  • Now, select Entire row and press OK.

delete dialog box

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

delete row output

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.

Delete Sheet Rows Option for Deleting All Rows Below a Certain Row

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

Delete Sheet Rows Option Final Output

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.

Using Name Box to Delete All Rows Below a Certain Row

  • 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.

Use of name box in excel

Read More: How to Use Macro to Delete Rows Based on Criteria in Excel (3 Ways)


Similar Readings:


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.

Using VBA Code to Delete All Rows below a Certain Row

  • 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

VBA code to Delete All Rows Below a Certain Row

  • 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.

VBA output to delete 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.

Deleting All Rows below the Last Active Row in Excel

  • Press CTRL+SHIFT+.
  • Press CTRL+SHIFT+.

Use of keyboard shortcuts to select rows

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

hide option from context menu

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

Deleting All Rows below the Last Active Row Output

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

Deleting All Rows below the Last Active Row with a VBA Code

  • 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.

Use of vba to in excel

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

vba dialog box

  • Lastly, you will get the following result.

VBA Code Output to Delete rows

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.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo