How do I reduce the worksheet size ?

[Guest]

I have a sheet that has buttons on for running macros. How do I just have the
area of worksheet visable so people cannot scroll across or down. I don't
wan't to freeze panes, I would like to do away with the unused cells
altogether?

 

[Jack Schitt]

You could try hiding all of the columns to the right of the last used
column, and hiding all of the rows below the last used row.

HTH

 

[Gord Dibben]

Kevin

You cannot do away with the unused cells. A worksheet has 256 columns and
65536 rows.

You can hide the rows and columns you don't want to see as Jack suggests.
Select them and Format>Hide.

Alternative.........You may want to try setting the Scroll Area.

This setting will not remain after closing the file.
Place a code line in the Workbook open event to set it
each time when you open the workbook.

Right-click on the Excel Logo left of your "File" on menu. Select "View Code"
and paste the following in there. Save the workbook and close/re-open to see
the scrollarea fixed to the range A1:H40

Private Sub Workbook_Open()
Sheets("Sheet1").ScrollArea = "A1:H40"
End Sub

Adjust to your sheetname and range.

Gord Dibben Excel MVP

 

[Guest]

That works--thanks--but seems like excell would have that function built in
some where

 

[Don G - ExcelForums.com]

Thanks Gord

Am new to this forum but used your suggested code line and it work
great for my purposes

I also have an "Intro" sheet that has numerous buttons on it an
didn't want the op to be able to scroll the page. After setting u
my buttons, using snap to align them....I increased cell 1A in bot
heigth and width such that it filled a little more than the screen.
Then set a background color. Then got rid of the scroll bars in th
OpenWorkbook module but the op could still scroll off the page wit
either the arrow keys or the mouse, but with your line of code
modified to fit my sheet

Sheets("INTRO").ScrollArea = "A1:A1

He/She's stuck in 1A.........:

Thanks for the direction here

Do