Limit of Rows in Excel Worksheet
Limit of rows in excel is a great tool to add more protection to the spreadsheet as this limits the other users to change or modify the spreadsheet to a great limit. While working on a shared spreadsheet this is important that a user must restrict the other user from changing the data that the primary user has inserted and this can be done by the excel row limit.
How to Limit Number of Rows in Excel? (with Examples)
Limit of Rows in Excel can be done in multiple ways as below.
- Hiding the rows
- Protecting the rows
- Scrolling limits
Example #1 – Limit of Rows by Using Excel Hide Function.
This is the easiest function that can be used to limit the rows that are in excel. By using this method we actually physically make the unwanted rows disappear from the workspace.
- Step 1: Select the rows that are not wanted and need to be restricted. In this case, we have selected the rows from A10 to the last rows.
- Step 2: Now after the rows are selected, right-click on the mouse and choose the option that will hide the rows.
- Step 3: After the rows are selected to be hidden then the user will only see those rows that are not set as hidden and in this way the rows are limited in excel.
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 this way we can restrict the user to have access to 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.
- Step 2: Now go to the Review Tab.
- Step 3: Now select the option of “Protect Sheet”.
- 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
- Step 5: Now 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 field 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: From the format cells option, choose the option to unprotect the range.
- 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 locked from accessing.
Step 1: Right-click on the sheet name and click on “View Code”
- Step 2: Now go to the view tab and select the properties window. You can also use the shortcut key F4 to select the properties window.
- Step 3: Now go to the scroll area and enter the rows that are to be made available to the user.
- Step 4: Now the user will only be able to access the first 10 rows in excel.
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 set to be as inactive, this means that they are only not available in the current worksheet and can be accessed in a new worksheet.
- If we are using the scroll lock option to inactivate some of the rows the only the current worksheet will be affected. 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 impacted and the rows will not get a reassigned number if any of the rows are hidden. Suppose if we have hidden the first 10 rows then this does not mean that 11th row will get 1st The 11th row will remain 11th row. This is because of the fact that excel wants the user to notify that some of the rows are hidden.
- If we are using the scroll 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 as protected.
- If we do not want other users to make changes to any of the rules that we have created related with the available rows then we must use the option of “Protect sheet” and then continue with a password.
This has been a guide to Excel Rows Limit. Here we discuss how to limit the rows in excel using 1) hide feature 2) Protect worksheet and 3) VBA Scroll Lock along with practical examples and downloadable templates. You may learn more about excel from the following articles –