How do I limit number of rows and columns on a spreadsheet

[Valser]

How do I restrict what I see on my screen (and specifically, limit the scroll
feature) to the rows and columns I'm actually using in an Excel 2003 Pro
spreadsheet?

 

[Gord Dibben]

Valser

Hiding the unused rows and columns then protecting the sheet is one method.

Setting the scrollarea using VBA is another method.

Note: Setting ScrollArea is good for that session only and only the
activesheet. Has to be reset next time workbook is opened.

Best to place the code into a WorkBook_Open Sub in ThisWorkbook module and
specify which worksheet.

Private Sub WorkBook_Open()
Sheets("YourSheet").ScrollArea = "A1:M36"
End Sub


Gord Dibben Excel MVP

 

[Valser]

Thank you, Gord, for your quick response. However, I am not an experienced
enough user to understand your answer. I need non-technical language, step
by step. I looked up the suggestions you gave in the Microsoft Excel Help,
but could not understand the directions there either. It's a question of the
technical semantics - defining terms. If you have the patience to deal with
a rank beginner, I'd appreciate detailed step-by-step - where to place the
cursor or which menu to select from - instruction. Thanks, again. Valser

 

[Gord Dibben]

OK.

To hide unused rows and columns manually.........

Select first column to the right of your desired area to show.

Hit SHIFT + END + Right Arrow to select all columns.

Format>Hide.

Do same for rows below the desired area to show.

If you want to limit the scrollarea.........

Copy the code I provided.

Right-click on the Excel logo left of "File" on the menubar.

Select "View Code"

Paste the code in there.

Revise the "yoursheet" to the name of the sheet you are on and adjust the
"A1:M36" range to suit your needs.

ALT + Q to go back to Excel.

Set your Tools>Options>Security>Macro Security to "Medium".

Save and close the workbook.

Re-open and click "enable macros" at the message.

You should not be able to scroll below or to the right of your scrollarea
range set in the code.


Gord