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

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

3 Effective Ways to Limit Number of Rows in Excel

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.

1. Hiding Rows to Limit Number of Rows

The easiest way to limit the rows in an Excel worksheet can be hiding 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.

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

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

2. Developer Property to Disabling Scrolling Rows Area

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.

• 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. Setting Scrolling Area Using VBA to Limit 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.

• 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

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. Which 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 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, copy and then pasting the code in the sheet module won’t work.

Practice Section

Here, I’m giving you the dataset of this article so that you can practice these methods on your own.

Conclusion

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