limiting the viewable space to the data shown

  • Thread starter Response to nesting problem
  • Start date
R

Response to nesting problem

I have seen other excel spreadsheets where you could only scroll right or
down so that only the data on the sheet is what you see and you cant go past
that. I am using excel 2000 and just cant seem to figure out how to do it.
 
V

VBA Noob

Hide the rows and or columns.

In tools > Options > View you can remove the scroll bar options

VBA Noob
 
G

Greg Wilson

Sub x()
ActiveSheet.ScrollArea = "A1:T30"
'To reset to normal: ActiveSheet.ScrollArea = ""
End Sub
 
G

Gord Dibben

Since the scrollarea method does not stick between sessions you will have to
reset it each time.

You may wish to place the code into a WorkBook_Open Sub in ThisWorkbook module
and specify which worksheet if only one sheet required.

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

Or also in the Thisworkbook module to limit scrollarea on all sheets.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
With ActiveSheet
.ScrollArea = "A1:T30"
End With
End Sub


Gord Dibben MS Excel MVP
 
R

Response to nesting problem

ok for those of you that dont know me...lol...you will have to use english
and not excel gibberish. Greg, Gord, and VBA, I'm sure in your world what
you said makes perfect sense, but to me it is just greek right now.
 
P

Pete_UK

Imagine you are using 12 columns and 60 rows in your spreadsheet. You
want to hide the columns and rows outside this area.

Highlight the 13th column (M) by clicking on the M in the column
identifier. Hold the <shift> key down and press <end> once followed by
<right-arrow> then release <shift>. This will have highlighted all the
columns except the first 12. Then click Format | Columns | Hide.

Similarly, click the row identifier for row 61 to highlight it. Then
hold the <shift> key down and press <end> once followed by <down-arrow>
then release <shift>, to highlight all the rows except for the first
60. Then click Format | Rows | Hide.

You can then press CTRL-Home to get you back to cell A1, and now when
you scroll you will only be able to cover the area of 12 columns and 60
rows.

Hope this explains it for you.

Pete
 
R

Response to nesting problem

Pete thank you very much...now just to keep mouse wheel from doing the
same...lol Thank You again.
 
Top