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

Get FREE Advanced Excel Exercises with Solutions!

There’s something common and irritating about spreadsheets with unnecessary rows or blank rows. But no worries, there are several ways in Excel to avoid these unwanted rows and they are quite easy to delete. In this article, we’re going to take a look at 5 ways to delete infinite rows from our Excel data.

overview of deleting infinite rows in excel


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: Changing Sheet Properties to Delete Infinite Rows

Let me introduce our dataset first. For this operation, I have made a dataset that represents some products’ names, order dates, and prices.

Dataset Containing some products’ names, order dates, and prices

We can change the sheet properties and make the number of active cells as much as we need. In that way, the number of active cells will be less and the infinite rows will be deactivated. Follow the steps below.

🔶 Steps:

  • At first, we will go to the Developer tab in the Ribbon. Click on Properties under the Control A dialog box will appear.

Going to sheet properties via developer tab

  • Second, we will go to the Scroll Area in the box and write our desired range in the adjacent cell. Here our excel sheet has active data from A1 till E12. So we will write $A$1:$D$12 in the cell and press Enter.

implementing active row range

  • Now we will see we can not select cells past this $A$1:$D$12 range.

working cursor can not pass the active cells resulting in deleting the infinite rows in excel

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.

🔶 Step 1: Using Home Tab to Select Special Criteria

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

Using home tab to go to 'go to special menu'

🔶 Step 2: Finding Out Blank Rows

  • Give mark on Blanks option.
  • Then just press OK.

selecting blanks to highlight blank rows

The blank rows are now highlighted in the dataset.

🔶 Step 3: Deleting Blank Rows In Excel

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

We’ll get a new dialog box then.

deleting blank rows in excel

🔶 Step 4: Shifting All the Rows up to Fill up the Blank Space

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

re-organizing the existing data

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

result after deleting infinite rows in excel

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.

selecting delete sheet rows from the home tab after selecting the rows to be deleted

  • You will find that the rows are no more now.

result after deleting infinite rows in excel

Read More: How to Delete Multiple Rows in Excel with Condition (3 Ways)


Similar Readings


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: Triggering Filter Menu

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

triggering filtering menu from data tab

🔶 Step 2: Selecting Blank Cells

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

selecting blanks to delete infinite blank rows in excel

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

filter result showing the blank rows

🔶 Step 3: Deleting Blank Rows

  • Pick those blank infinite rows and right-click your mouse.
  • Press Delete Row from the context menu.

deleting infinite rows in excel

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

🔶 Step 4: Reselecting the Data

  • Click the filter icon again.
  • Give mark on the (Select All) option.
  • Finally, press OK.

re-organizing all the data

Here’s our other filled rows are back now.

result after deleting infinite rows in excel

Read More: How to Delete Filtered Rows in Excel (5 Methods)


Method 5: Embed Excel VBA to Limit Scroll Area

If you like to work with VBA in Excel then I can give you the easiest way to erase 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: Triggering VBA Window

  • Right-click your mouse on the sheet title.
  • Select View Code from the context menu.

triggering vba code window from context menu

A VBA window will open up.

🔶 Step 2: Inserting the code

  • Now type the codes given below.
Sub All_WC_SCROLL LIMIT()

Dim ws As Worksheet

For Each ws In Application.ActiveWorkbook.Worksheets
      ws.ScrollArea = ws.UsedRange.Address
Next

End Sub

inserting and running the code

  • Then press the Play icon to run the codes.
  • Finally, you will notice the result below. We will not be able to go beyond the used cell limit.

permanently deactivating the infinite rows in excel with vba

Read More: How to Filter and Delete Rows with VBA in Excel (2 Methods)


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

Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo