How to Delete Rows in Excel That Go on Forever?

Nowadays, Excel is the most versatile tool for analyzing and visualizing data. Most people in the office and businesses use this software on a day-to-day basis. While working in Excel we may need to delete rows for many reasons. Like, there might be irrelevant data or blank cells. So, it’s a pretty necessary task. In this article, we’ll demonstrate 5 basic and easy methods to delete rows in Excel that go on forever. So, go through the entire article to understand the whole topic properly.

how to delete rows in excel that go on forever

Not to mention, here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience. Please leave a comment if any part of this article does not work in your version.

How to Delete Rows in Excel That Go on Forever: 5 Easy Methods

For ease of understanding, we are going to use Details of Products of a certain clothing store. This dataset includes the Product Code, Product Name, and Quantity in columns B, C, and D respectively. Using this dataset, we’ll try to achieve our goal. So, let’s explore them one by one.

how to delete rows in excel that go on forever

Note: This is a basic dataset to keep things simple. In a practical scenario, you may encounter a much larger and more complex dataset.

1. Using Context Menu

In our very first method, we’ll use the manual method to delete rows in Excel. Let’s follow the steps below to do this using this method.

📌 Steps:

➽ At first, select the rows that you want to delete.
➽ Then, right-click your mouse.
➽ After that, select Delete from the Context Menu.

Using Context Menu to delete rows in excel that go on forever

Immediately, you’ll notice that the selected rows are deleted now.

Deleting rows in Excel that go on forever using the context menu

2. Utilizing Delete Command from Ribbon

In this method, we’ll delete rows in Excel that go on forever using the Delete tab from the Home ribbon.

📌 Steps:

➽ Firstly, select the rows.
➽ Later, click as follows: Home > Cells > Delete > Delete Sheet Rows.

Utilizing Delete Command on Ribbon

Then, instantly, you will notice that those rows are no more.

Deleting rows that go on forever Utilizing Delete Command on Ribbon

3. Applying Find & Select Tool

Here, we’ll use the Find & Select tool to delete blank rows in Excel that go on forever. This is useful when you’re working with large spreadsheets or when you need to change certain text repeatedly. For this method, I have placed two blank rows in my dataset.

📌 Steps:

➽ At this time, select the data range (B4:D11).
➽ Correspondingly, click Home > Editing > Find & Select > Go To Special….

Applying Find & Select Tool in Excel

Suddenly, a dialog box named “Go To Special” will appear.
➽ Mark on the Blanks option.
➽ Press OK.

working on Go To Special dialog box to delete rows in Excel

The blank rows are now highlighted.
➽ Then right-click your mouse and select Delete from the Context Menu.

bringing up context menu

A new Delete dialog box will open up.
➽ Mark the choice of the Entire row and press OK.

Delete dialog box to delete entire rows in Excel

Now you’ll spot that the blank rows are gone forever.

Applying Find & Select Tool to delete rows in excel that go on forever

4. Implementing Filter Option

Filtering data in Excel refers to displaying only the rows that meet certain conditions. We’ll apply it here in our last method to remove blank rows in Excel.

📌 Steps:

➽ Initially, select the data range including the title.
➽ Then, click in the sequence: Data > Filter.

Implementing Filter Option to delete rows in Excel

Then, you will see the filter icon beside every heading of your dataset.
➽ At this moment, press any filter icon, and the filtering options will open up.
➽ Then, unmark everything except (Blanks).
➽ As usual, press the OK button.

marking blank cells in Filter

As a result, it is showing only the blank rows now.

Showing Blank Rows in Excel sheet

➽ Afterward, select those blank rows and right-click your mouse.
➽ Now, click on Delete Row from the Context Menu.

Selecting rows to delete them forever in Excel

Magically, the blank rows are deleted. Now, we’ll execute the following steps to get back the other rows.
➽ This time, click on the filter icon again.
➽ Then, mark the (Select All) option.
➽ Finally, press OK.

returning other rows in Excel

Our filled rows are back now.

Implementing Filter Option to delete rows in excel that go on forever

Related Content: How to Delete Filtered Rows in Excel?

5. Incorporating VBA Macro

You might be wondering, is there a way to automate this task? Then VBA has you covered. Just follow along.

📌 Steps:

➽ Firstly, go to Developer tab >> Visual Basic option.

opening Visual basic from the developer tab

Then, the Visual Basic Editor will open up.
➽ Secondly, go to Insert tab >> Module option.

VB editor in Excel

After that, a Module will be created.
➽ Thus, paste the following code into the module.

Sub Dlt_Row()
End Sub

VBA code to delete rows in excel that go on forever

➽ Now, click on the Run icon or press the F5 key on the keyboard.

Executing the code

Magic! Look at the worksheet now. The desired rows are gone.

Incorporating VBA Macro to delete rows that go on forever in Excel

How to Delete Columns That Go on Forever in Excel

Like before, we also can delete columns in Excel that go on forever. It’s simple and so easy. Just follow along.

📌 Steps:

➽ At first, select columns C and D.
➽ Then, right-click anywhere inside these two column headings.
➽ After that, select the Delete option from the Context Menu.

Delete Columns in Excel That Go on Forever

Look, they are gone now.

only one column is present now

Practice Section

For doing practice by yourself, we have provided a Practice section like the one below on the right side of the sheet. Please do it by yourself.

Practice Section

Download Practice Workbook

You may download the following Excel workbook for better understanding and practice.


This article explains how to create an invoice in Word from Excel data in a simple and concise manner. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions.

Related Articles

<< Go Back to Delete Rows | Rows in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF