Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Apr 2002
    Location
    Maryland
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Limit Scroll bar (97 SR2)

    How can I limit the vertical scroll bar to at or near the end of my data? I may have only 80-100 entries, yet it will sometimes scroll down to blank lines in the thousands.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Limit Scroll bar (97 SR2)

    If the scrollbar "ribbon" isn't at the bottom of the scrollbar when you are at the last row of data, the most likely reason is that there are cells formatted to something other than default extending below the last data rows. Simplest way to eliminate the problem is to delete all rows below the data.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Limit Scroll bar (97 SR2)

    The solution offered made sense to me but when I tested it, I did not get favorable results. Maybe I missed something?

    First I opened a blank workbook and filled the first 100 cells in the A column. The v scrollbar worked perfectly, it just scrolled the data.

    Then I manually scrolled down to cell A65536 and applied the Bold format to the cell, no data. Now the v scrollbar will scroll all the way to 65536.

    I removed the formatting from the one cell where it was applied (A65536), deleted cell A65536, deleted all cells A101:A65536, deleted row 65536, then deleted all rows from 101 to 65536. After each of these steps, the v scroll bar never returned to where it only scrolled the 100 cells of data
    - Ricky

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limit Scroll bar (97 SR2)

    After deleting the (entire!) row(s), save the workbook.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limit Scroll bar (97 SR2)

    Another solution is to set the sheet's ScrollArea to some set range of cells. You can manually add this to the sheet's property or do it in code. I do this when I have some data below some data entry areas that I don't want them messing with (and then I set their font color to the background color).

    Deb <img src=/S/chatter.gif border=0 alt=chatter width=38 height=16>

  6. #6
    New Lounger
    Join Date
    Apr 2002
    Location
    Maryland
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limit Scroll bar (97 SR2)

    Tried John's idea of deleting the rows and did not work for me either. How do I manually set theScroll Area to a range as you suggested in your post? I didn't see anything in the properties box which might apply. Thanks.

    Sue

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Limit Scroll bar (97 SR2)

    In gentle defense of my incomplete suggestion, you must entirely delete all rows below the last used row (which has the same effect as Edit, Clear, All for those unused rows), Save the Workbook, Close it and reopen it. Then the horizontal scroll bar ribbon will correctly show display position vs data rows.
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limit Scroll bar (97 SR2)

    To reset the empty rows for Excel you need to delete the rows, then save and exit Excel. When you re-open the workbook these old empty rows will not be considered part of the worksheet.

    The 'scroll area' is a property of the worksheet. You can view this by clicking on any cell and then click the 'properties' icon from the Visual Basic toolbar. This will display the standard properties dialog for the selected object (a button, a form, worksheet, etc.) Toward the bottom of this dialog you'll see 'ScrollArea' and it takes an address of contigous cells like $A$1:$L$50. This now restricts access to cells w/in that range. It works even if the sheet is not protected.

    Deb

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Limit Scroll bar (97 SR2)

    Deb,

    The Scroll area property does not stick after a file close, and needs to be reset when the workbook is opened.

    Andrew C

  10. #10
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limit Scroll bar (97 SR2)

    Ahh, good catch. All my projects use VBA so I forgot about that. It can be set with the worksheet_activate event like this:

    activesheet.scrollarea = "$A$1:$M$20"

    Deb

  11. #11
    New Lounger
    Join Date
    Apr 2002
    Location
    Maryland
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limit Scroll bar (97 SR2)

    Thanks for the redirect; worked just fine!

  12. #12
    New Lounger
    Join Date
    Apr 2002
    Location
    Maryland
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limit Scroll bar (97 SR2)

    Thanks so much to John, Deb and all the knowledgeable loungers who helped me out. Problem solved!!

Posting Permissions

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