Closed Thread
Results 1 to 5 of 5

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

  1. #1
    Valser
    Guest

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

    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?


  2. #2
    Forum Contributor
    Join Date
    08-23-2004
    Posts
    210
    Highlight all the columns to the right of the spreadsheet, and click on Hide (Windows, Hide, in my Excel97). Now highlight all the rows below the spreadsheet and hide them too. You must highlight to the ends of the whole spreadsheet each time). Name and save the spreadsheet. When the spreadsheet is reopened, the viewable sheet will be as small as you made it, and the scrolling will be limited too.

  3. #3
    Gord Dibben
    Guest

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

    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

    On Sat, 12 Feb 2005 10:05:03 -0800, Valser <[email protected]>
    wrote:

    >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?
    >



  4. #4
    Valser
    Guest

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

    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" wrote:

    > 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
    >
    > On Sat, 12 Feb 2005 10:05:03 -0800, Valser <[email protected]>
    > wrote:
    >
    > >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?
    > >

    >
    >


  5. #5
    Gord Dibben
    Guest

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

    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



    On Sat, 12 Feb 2005 13:43:07 -0800, Valser <[email protected]>
    wrote:

    >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" wrote:
    >
    >> 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
    >>
    >> On Sat, 12 Feb 2005 10:05:03 -0800, Valser <[email protected]>
    >> wrote:
    >>
    >> >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?
    >> >

    >>
    >>



Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1