How to Delete Infinite Rows in Excel (5 Easy Ways)

There’s something common and irritating about spreadsheets with unnecessary rows or blank rows. But the good news is there are lots of ways to get rid of these unwanted rows and they can be pretty easy to delete. In this article, we’re going to take a look at 5 ways to remove infinite rows from our Excel data.


Download Practice Book

You can download the free Excel template from here and practice on your own.


5 Easy Ways to Delete Infinite Rows in Excel

Method 1: Use Selection From the Context Menu to Remove Infinite Rows in Excel

Let me introduce our dataset first. For this operation, I have made a dataset that represents some products’ names, order dates and prices. In our very first method, I’ll use the manual method to delete rows in excel.

Selection From the Context Menu to Delete Infinite Rows in Excel

Steps:

➽ First, select the rows that you want to delete.

➽ Then right-click your mouse.

➽ Select Delete option from the context menu.

Selection From the Context Menu to Delete Infinite Rows in Excel

You’ll notice that the selected rows are removed now.

Read more: How to Delete Selected Rows in Excel


Method 2: Apply Find & Select Tool to Erase Infinite Rows in Excel

In this method, I’ll use the Find & Select tool to delete blank infinite rows in excel. It will be useful when you’re working with large spreadsheets. For this method, I have placed two blank rows in my dataset.

Step1:

➽ Select data range from the dataset.

➽ Click successively: Home > Editing > Find & Select > Go To Special

A dialog box named “Go To Special” will open up then.

Find & Select Tool to Erase Infinite Rows in Excel

Step 2:

➽ Give mark on Blanks option.

➽ Then just press OK.

Find & Select Tool to Erase Infinite Rows in Excel

The blank rows are now highlighted in the dataset.

Step 3:

➽ Then right-click your mouse and click Delete from the context menu.

We’ll get a new dialog box then.

Find & Select Tool to Erase Infinite Rows in Excel

Step 4:

➽ Put a mark on Entire row option and press OK.

Now you’ll observe that the blank infinite rows are gone.

Read more: How to Delete a Row If a Cell is Blank in Excel


Method 3: Use Delete Tab to Delete Infinite Rows in Excel

Here, we’ll do the same operation using the Delete tab from the Home ribbon. It’s quite easy.

Steps:

➽ Select the rows that you want to remove.

➽ Click as follows: Home > Cells > Delete > Delete Sheet Rows

Delete Tab to Delete Infinite Rows in Excel

You will find that the rows are no more now.


Method 4: Insert Filter Option to Remove Infinite Rows in Excel

The FILTER option filters a range of data based on supplied criteria. We’ll apply it here in our method to delete some blank infinite rows in excel.

Step 1:

➽ Including your title select the data range.

➽ Then click: Data > Sort & Filter > Filter

You will get the filter icon in every title box of your dataset.

Filter Option to Remove Infinite Rows in Excel

Step 2:

➽ Click any filter icon and then the filtering options will open up.

➽ Now unmark everything without the (Blanks) option.

➽ Just hit the OK tab then.

Filter Option to Remove Infinite Rows in Excel

You will spot that it is showing only the blank rows now.

Filter Option to Remove Infinite Rows in Excel

Step 3:

➽ Pick those blank infinite rows and right-click your mouse.

➽ Press Delete Row from the context menu.

Those blank rows are deleted. Now we’ll try to get back our other rows.

Step 4:

➽ Click the filter icon again.

➽ Give mark on the (Select All) option.

➽ Finally, press OK.

Here’s our other filled rows are back now.


Method 5: Embed Excel VBA to Delete Infinite Rows in Excel

If you like to work with VBA in Excel then I can give you the easiest way to erase infinite rows in Excel.

Step 1:

Right-click your mouse on the sheet title.

➽ Select View Code from the context menu.

A VBA window will open up.

Excel VBA to Delete Infinite Rows in Excel

Here I’ll delete the blank rows from 7 to 9. You can change the criteria in the given codes.

Step 2: 

➽ Now type the codes given below.

Option Explicit
Sub sbDeleteARowMulti() 
Rows("7:9").Delete 
End Sub

➽ Then press the Play icon to run the codes.

Finally, you will notice in our image below, the rows from 7 to 9 are deleted.

Read more: Delete Multiple Rows in Excel with VBA


Conclusion

I hope all of the methods described above will be helpful enough to delete infinite rows in excel. Feel free to ask any questions in the comment section and please give me feedback.


Related Articles

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Excel is an amazing softwear. Here i will post excel related useful articles. I am a graduate from Bangladesh University of Engineering and Technology. I love to learn new things and work with it. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo