Excel is one of my passions. It is such a versatile spreadsheet package that allows users to organize, format, and calculate data with the use of formulas or functions tools. The Excel spreadsheet system is broken into rows and columns. Excel 2007/2010 versions has “unlimited” rows and columns which in most cases will not be used. Instead of scrolling endlessly, limit the rows and columns by changing a setting with the use of VBA.Limit Rows and Columns in Excel – Temporarily
Even though each worksheet in Excel 2007 or 2010 can have more than 1,000,000 rows and more than 16,000 columns it’s not often that we need that much room.Mostly we use considerably fewer than the maximum number of rows and columns and sometimes it might be an advantage to limit access to unused areas of the worksheet.For example, to avoid accidental changes to certain data it is sometimes useful to place it in area of the worksheet where it can’t be reached.Or, if less experienced users need to access your worksheet limiting where they can go can keep them from getting lost in the empty rows and columns that sit outside the data area.
Whatever the reason, you can temporarily limit the number of rows and columns accessible by changing the Scroll Area property of the worksheet.
For help with this example, see the image above.
Note: This setting is retained only so long as a workbook is open. Once it is closed any changes to the scroll area are removed.
In this example we will change the properties of a worksheet to limit the number of rows to 30 and the number of columns to 26.
- Open a blank Excel file.
- Right-click on the sheet tab at the bottom right of the screen for Sheet 1.
- Click on View Code in the menu to open the Visual Basic for Applications (VBA) editor window.
- Find the Sheet Properties window in the bottom left corner of the VBA editor window.
- Find the Scroll Area property in the list of worksheet properties.
- Click in the empty box to the right of the Scroll Area label.
- Type a1:z30 in the box.
- Click on File > Save in the menus to open the Save as dialog box.
- Choose a filename and location and click Save to save the worksheet.
- Close the VBA editor window and return the worksheet.
- Test the worksheet. You should not be able to scroll below row 30 or to the right of column Z.
- In addition you should not be able to click on a cell beyond Z30 in the worksheet.
- To remove the scroll restrictions, close and reopen the workbook.
Have you ever thought about your excel sheets setup?
Website: Technoview