How to Limit Number of Rows in Excel (3 Effective Methods)

Get FREE Advanced Excel Exercises with Solutions!

The article will show you how to limit the number of rows in Excel. An Excel worksheet has 1048576 rows and 16384 columns and we don’t need all these rows to work with. Sometimes our dataset can contain a few sets of rows. In that case, limiting the number of rows may give us a feasible workspace in the Excel sheet. The purpose of this article is to provide you with some basic tips to limit the number of rows in an Excel worksheet.

In the dataset, you will see sales information about a grocery store. The amount of sales items in that store is not that much. So we don’t need all the rows in the sheet to calculate total sales or profits. You will see the processes of limiting the rows in that sheet in the later section of this article.

limit number of rows in excel


1. Hiding Rows to Limit Number of Rows in Excel

The easiest way to limit the rows in an Excel worksheet can be to hide them from that sheet. Let’s go through the instructions below for a better perspective.

Steps:

  • Select an empty row after your dataset. In my case, I want to keep up to the 14th row of the sheet. So I selected the 15th.

  • After that, press CTRL+SHIFT+Downward key. It will select all the remaining empty rows in that sheet.
  • Thereafter, right-click on anywhere of the selected cells and select Hide.

  • However, you may use the same command from the Cells group of the Home Tab. You need to select Format >> Hide & Unhide >> Hide Rows.

limit number of rows in excel

  • After this command, all the empty rows after the 14th will not be available to use.

limit number of rows in excel

Thus you can limit the number of rows in Excel just by hiding them.

Read More: How to Handle More Than 1048576 Rows in Excel


2. Set Scroll Area in Properties Control of Developer Tab

In this section, I’ll show you how to limit the row numbers by disabling the scroll area using the Developer Property for an Excel sheet. However, this is a temporary process. If you close your workbook and open it again, it won’t work. Let’s go through the steps below for a better understanding.

Steps:

  • First, go to Developer >> Property.

  • After that, type the range that you want to be enabled for scrolling in the Properties In my case, the range is $1:$15.

limit number of rows in excel

  • Finally, close the Properties This operation will disable the scrolling area outside this range of rows. You cannot select them, so your number of usable rows is now limited.

Thus you can limit the number of rows by disabling the scroll area.


3. Use VBA Code to Set Scroll Area to Limit the Number of Rows

You can also use a VBA code to limit the number of rows permanently. Let’s go through the procedure below for a better understanding.

Steps:

  • First, go to the Developer Tab and then select Visual Basic.

limit number of rows in excel

  • After that, open the VBA sheet from the VBAProject window and activate Worksheet from the marked drop-down icon of the following image.

  • Now, type ‘ScrollArea = “A1:XFD15”’ as the statement. The overall code will look like this.
Private Sub Worksheet_Activate(ByVal Target As Range)
Me.ScrollArea = "A1:XFD15"
End Sub

limit number of rows in excel

Here, we activate the worksheet for Private Sub Procedure and set the desired scrolling area. In this case, I set the range A1:XFD15 for scrolling. This means it’s impossible to select any cell outside this range and thus we can limit the number of rows to operate.

  • Finally, press CTRL+S to save the file and go back to your sheet. In this case, the name of the sheet is the workbook. You won’t be able to select any of the cells outside the range and scroll down even though you close the Excel file and open it again.

Thus you can limit the number of rows by using VBA.

Note:

You must activate the worksheet from the drop-down icon that we mentioned in the steps. Otherwise, copying and then pasting the code in the sheet module won’t work.

Read More: How to Set the End of an Excel Spreadsheet


Download Practice Workbook


Conclusion

Suffice it to say, that you can learn some basic tactics to limit the number of rows in Excel after reading this article. If you have any better methods questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles.


Related Articles


<< Go Back to Row and Column Limit | Rows and Columns in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Meraz Al Nahian
Meraz Al Nahian

Md. Meraz Al Nahian has worked with the ExcelDemy project for over 1.5 years. He wrote 140+ articles for ExcelDemy. He also solved a lot of user problems and worked on dashboards. He is interested in data analysis, advanced Excel, statistics, and dashboards. He also likes to explore various Excel and VBA applications. He completed his graduation in Electrical & Electronic Engineering from Bangladesh University of Engineering & Technology (BUET). He enjoys exploring Excel-related features to gain efficiency... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo