Row Limit in Excel

Last Updated :

-

Blog Author :

WallStreetMojo Team

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

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.

Table of contents

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

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

  1. Hiding the rows
  2. Protecting the rows
  3. Scrolling 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.

Below are the steps for limiting rows using the Excel hide function:

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


    Excel Row Limit Example 1

  2. Now, right-click on the mouse after the rows are selected and choose the option that will hide the rows.


    Excel Row Limit Example 2

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


    Excel Row Limit Example 3

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 excel. This way, we can restrict the user from accessing the restricted rows.

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.
Excel Row Limit Example 2
  • Step 2: Now, go to the "Review" tab.
Excel Row Limit Example 2-1
  • Step 3: Now, select the option of "Protect Sheet."
Excel Row Limit Example 2-2
  • Step 4: From the options of protecting the sheet, Untick the option "Select locked cells." By not checking this option, Excel will now not allow the users to select the locked cells.
Excel Row Limit Example 2-3
  • 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.
Excel Row Limit Example 2-4
  • Step 7: Now, right-click and choose the option of "Format Cells."
Excel Row Limit Example 2-5
  • Step 8: Choose the option to unprotect the range from the "Format Cells" option.
Excel Row Limit Example 2-6
  • Step 9: Now, the rows are limited, as only the selected rows are accessible by the user.
Excel Row Limit Example 2-7

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

Step1 - Right click on sheet name
  • 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 2
  • Step 3: Now, go to the “ScrollArea” and insert the rows to be made available to the user.
Step 3
  • Step 4: The user can only access the first 10 rows in Excel.
Step 4

Things to Remember

  • By doing the limit of rows in Excel, we only choose to hide the rows that are not yet required.
  • When rows are inactive, they are only not available in the current worksheet and can be accessed in a new worksheet.
  • Only the current worksheet will be affected if we use the scroll lock option to inactivate some of the rows. Other sheets will not get impacted as the property is only changed for that worksheet, whose code is viewed and then changed.
  • The rows number will not get affected, and the rows will not get a reassigned number if any of the rows are hidden. If we have hidden the first 10 rows, this does not mean that the 11th row will get first. The 11th row will remain the 11th row since Excel wants the user to notify that some rows are hidden.
  • If we are using the "ScrollArea" option to limit the rows, then this can be changed by the other user as this option to change the scroll option is available to all users as this option does not make the changes protected.
  • If we do not want other users to change any of the rules we have created related to the available rows, then we must use the option of “Protect sheet in excel” and then continue with a password.