How Do You Limit the Number of Rows in an Excel Spreadsheet?
If you want to allow other people to edit one of your spreadsheets in Microsoft Excel 2013 but don't want them to add more rows, limit the number of rows using one of two methods. You can hide all the rows below the last row of your spreadsheet or temporarily prevent people from scrolling below a certain row. Excel 2013 spreadsheets can have a maximum of 1,048,576 rows; you can select all of them and hide them, even if the rows are blank. To temporarily disable scrolling below a specific row, you must edit the VBA properties of the spreadsheet.
Hide All Rows Below a Specific Row
Open the Excel spreadsheet by pressing "Ctrl-O," selecting the file and clicking "Open." Locate the last row you want to display in the spreadsheet.
Select the entire row below the last row you want to display by clicking the row number. Don't select a single cell in the row accidentally.
Hold "Ctrl-Shift" and press the "Down" arrow key to select all rows below the selected row. The last row is row number 1,048,576, so scrolling manually until you find it may take a while.
Click "Format" in the Cells group on the Home tab, select "Hide & Unhide" and then click "Hide rows" to hide all rows. Only the rows that were not selected can be edited now. Press "Ctrl-S" to save the spreadsheet.
Temporarily Disable Scrolling and Editing
Open the spreadsheet you want to edit, right-click the sheet name in the lower left corner of the window and choose "View Code" to open the VBA editor. Press "F4" to display the Properties window, if it is not displayed by default.
Click inside the "ScrollArea" field in the Properties window. Type the number of the first row you want to display, a colon and then the number of the last row you want to display. For example, to let people scroll and edit only rows 1 through 100, type "1:100" (without the quotes) into the field.
Press "Ctrl-S" and save the file on your hard drive. Close the Microsoft Visual Basic for Applications window and test the settings by scrolling through the spreadsheet. You will be unable to scroll past the row number you typed into the ScrollArea field. Even if you can see one more row under the last row, nothing happens when you click one of its columns. All other rows are not editable.
Tips & Warnings
- You can also hide columns to prevent people from editing them. Locate the last column you want to be visible in the spreadsheet and then click the header of the next column to select it. Don't try to scroll to the last column because Excel 2013 spreadsheets can contain 16,384 columns. Hold "Ctrl-Shift" and press the "Right" arrow key to select all columns. Click "Format," select "Hide & Unhide" and then click "Hide columns" to hide the columns.
- To unhide all rows, press "Ctrl-A" to select everything in the spreadsheet, click "Format" in the Cells group, select "Hide & Unhide" and then click "Unhide rows." To unhide all columns, click "Unhide columns."
- You can hide and unhide rows and columns that contain data, in case you don't want people to edit them accidentally.
- You can temporarily disable scrolling through -- as well as editing -- columns. In the ScrollArea field, type the letter of the first column you want to display, before typing the row number. Type the letter of the last column you want to display before the number of the last row. For example, to scroll through rows 1 through 100 and columns A through Z, type "A1:Z100" (without the quotes).
- When you disable scrolling, you also disable editing. Even if the rows are visible, nobody can edit them.
- The VBA properties are reset when you close and reopen the spreadsheet, so disabling scrolling and editing is just a temporary solution. Hiding the rows and columns is a permanent solution.