Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    UsedRange Scrollbar (2000 sp3)

    What controls the range of rows scrolled to by the scroll bar in a worksheet? I searched several places and can't find anything about this. If I start with a clean workbook and fill in 100 or so rows with data the scroll bar does dutifully scroll me through them. If I delete about 20 of the lines it sometimes resets the scroll bar and sometimes it doesn't. Other times it resets it after a short period of time or when I go into the VB editor!
    I have a macro that copies one sheet to another and then deletes a lot of the rows based on the criteria it matches. In this case the scroll bar continues to scroll through all the rows that were once used. Even the sheet.usedrange property gives me the whole range. How can I reset that so it accurately reflects the rows that are actually being used now and doesn't include the rows that are empty?
    Any help you can give me would be great!
    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: UsedRange Scrollbar (2000 sp3)

    You already seem to have figured out that
    1. <LI>The scroll range is related to what Excel sees as the used range of the worksheet.
      <LI>The used range isn't always what you would expect
    Excel will always expand the used range as cells are filled, but it will not always shrink it as cells are deleted or cleared. The used range will be reset when the workbook is saved, and executing VBA code that uses ActiveSheet.UsedRange is also supposed to reset it.
    Sometimes Excel considers cells that have specific formatting as part of the used range, even if the cell contents are blank.

  3. #3
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: UsedRange Scrollbar (2000 sp3)

    Ah, the formatting! That could well be a big part of my problem. The other issues are troubling but you've given me a good handle on working around it. Thanks!!!

Posting Permissions

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