How to Limit Rows and Columns in an Excel Sheet

  1. Show only what you want to see
  2. Limit number of rows in Excel with VBA
  3. Remove scrolling restrictions
  4. Hide rows and columns in Excel
  5. Show rows and columns in Excel

Show only what you want to see

Each sheet in Excel can contain more than 1,000,000 rows and 16,000 columns of information, but it’s very rare that this is all the space needed for everyday projects. Fortunately, you can limit the number of columns and rows that appear in your spreadsheet. In addition, you can restrict access to certain parts of the sheet.

For example, to prevent accidental changes to some data, you can place it in an area of ​​the worksheet where other users cannot access it. Or, if less experienced users need access to your spreadsheet, restricting where they can go prevents them from getting lost in empty rows and columns.

These instructions apply to Excel 2019, 2016, 2013, 2010, and Excel for Office 365.

Limit number of rows in Excel with VBA

You can use Visual Basic for Applications (VBA) to temporarily limit the range of rows and columns in a property Scroll area worksheet.

Changing the scroll area is a temporary measure; it resets every time the workbook is closed and reopened.

In this example, you change the sheet properties to increase the number lines up to 30 and quantity columns up to 26

  1. Open empty excel file.

  2. Right click sheet tab in the lower right corner of the screen for Page 1

  3. Click Show code in the menu to open the editor window Visual Basic for Applications (VBA)

  4. Find a window Blade properties in the lower-left corner of the VBA editor window.

  5. Find a home Scroll area in the sheet properties list.

  6. Press empty field to the right of scroll area

  7. Enter range a1:z30 in field.

  8. Save sheet.

  9. Click File > Close and return to Microsoft Excel.

Now check your worksheet to make sure your changes have taken effect. If you try to scroll through row 30 or column Z, you have to go back to the selected range in the spreadsheet. You cannot edit cells outside this range.

Remove scrolling restrictions

The easiest way to remove scrolling constraints is to save, close, and reopen the workbook. Alternative, use steps 2 to 4 above to open Blade properties in the window VBA editor and delete the specified range for role. Region property.

The picture shows the entered range as $A$1:$Z$30 † When you save a workbook, the VBA editor will add dollar signs to make cell references in an absolute range.

Hide rows and columns in Excel

An alternative method of limiting the work area of ​​a worksheet is to hide unused rows and columns; they remain hidden even after you close the document.

To hide out-of-range rows and columns, do the following A1: Z30

  1. Click rich head for line 31 to select the entire row.

  2. Press and hold keys Shift and ctrl on keyboard.

  3. Press and release the button Arrow down on the keyboard to select all rows from row 31 to bottom sheet.

  4. Right click in row heads to open context menu

  5. Select Hide in the menu to hide the selected columns.

  6. Click on the column header for column AA and repeat steps two to five to hide all columns afterwards column Z

    Short press the right arrow instead of the down arrow to hide the right columns.

  7. Save a book; columns and rows out of range of A1 before Z30 are hidden until you show them.

Show rows and columns in Excel

If you change your mind or make a mistake, you can view the rows and columns and start over.

To display row 31 and above and column Z and above:

  1. Click on the row header for line 30 – or the last visible row on the sheet – to select the entire row.

  2. Then browse by right clicking to the hidden section.

  3. Click Main tab on the adhesive tape

  4. In chapter cells Click Format Hide and show Show lines to restore hidden rows.

    You can also right-click on the row header and select “Show” from the drop-down menu.

  5. Click on the column header for column AA – or the last visible column – and repeat steps two to four above to display all columns.

Leave a Reply

Your email address will not be published. Required fields are marked *