Limit of Rows in Excel Worksheet

Limiting rows in Excel is a great tool to add more protection to the spreadsheet as this restricts the other users from changing or modifying the spreadsheet to a great limit. For example, while working on a shared spreadsheet, a user must limit the other user from changing the data that the primary user has inserted. The Excel row limit can do this.

How to Limit the Number of Rows in Excel? (with Examples)

We can limit rows in Excel in multiple ways, as below.

  • Hiding the rowsProtecting the rowsScrolling limits

Example #1 – Limit of Rows by Using Excel Hide Function.

It is the easiest function that we can use to limit the rows that are in Excel. Using this method, we physically make the unwanted rows disappear from the workspace.

Example #2 – Restrict Accessing Rows by Protecting the Worksheet

Another simple way of restricting the rows in excel is by protecting the sheet and disabling the feature of allowing the user to select locked cells in excelSelect Locked Cells In ExcelIn Excel, cells are locked to protect them from unwanted editing, deleting, and overwriting. Locking cells is specifically beneficial in cases where an excel worksheet needs to be shared with several colleagues.read more. This way, we can restrict the user from accessing the restricted rows.

  • First, select the rows that are not wanted and need to be restricted. In this case, we chose the rows from A10 to the last. Now, right-click on the mouse after the rows are selected and choose the option that will hide the rows. After the rows are selected to be hidden, the user will only see those not set as hidden. In this way, the rows are limited in Excel.

In this method, the user is only restricted from accessing the rows. However, all the rows are still visible to the users.

  • Step 1: Select the complete workbook.

  • Step 2: Now, go to the “Review” tab.

  • Step 3: Now, select the option of “Protect Sheet.”

  • Step 5: The complete worksheet is locked and cannot be accessed since we have protected the complete worksheet.Step 6: Now, we must unprotect the rows or fields that we want the users to have access to. Select the rows that are to be made available.

  • Step 7: Now, right-click and choose the option of “Format Cells.”

  • Step 8: Choose the option to unprotect the range from the “Format CellsFormat CellsFormatting cells is an important technique to master because it makes any data presentable, crisp, and in the user’s preferred format. The formatting of the cell depends upon the nature of the data present.read more” option.

  • Step 9: Now, the rows are limited, as only the selected rows are accessible by the user.

Example #3 – Limit Worksheet Rows in Excel with VBA (Scroll Lock)

In this method, the rows are blocked from accessing.

Step 1: Right-click on the sheet name and click “View Code.”

  • Step 2: Go to the “View” tab and select the “Properties Window.” You can also use the shortcut key F4 to choose the properties window.

  • Step 3: Now, go to the “ScrollArea” and insert the rows to be made available to the user.

  • Step 4: The user can only access the first 10 rows in Excel.

Things to Remember

This article is a guide to Excel Rows Limit. Here, we discuss how to limit the rows in Excel using the hide feature, protect the worksheet, and VBA scroll lock, along with practical examples and downloadable templates. You may learn more about Excel from the following articles: –

  • Count Rows in ExcelCount Rows In ExcelThere are numerous ways to count rows in Excel using the appropriate formula, whether they are data rows, empty rows, or rows containing numerical/text values. Depending on the circumstance, you can use the COUNTA, COUNT, COUNTBLANK, or COUNTIF functions.read moreVBA Last Row FunctionVBA Last Row FunctionThe End(XLDown) method is the most commonly used method in VBA to find the last row, but there are other methods, such as finding the last value in VBA using the find function (XLDown).read moreRemove Blank Rows in ExcelRow Function Excel Example