Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Dec 2013
    Posts
    22
    Thanks
    2
    Thanked 6 Times in 6 Posts

    VBA commands to preserve displayed rows?

    I have a simple sort macro where I save the active cell at the beginning and restore it at the end. I would like the worksheet to look the same at the end as it does when I execute the macro. For example, if the worksheet is displaying rows 20 through 75, and my active cell is A65, I want to see the same thing after the macro is executed. Instead, the display of rows changes so that the active cell's row is in the middle. In my example, the worksheet displays rows 37 through 92, with row 65 now in the middle of the displayed range of rows.

    Is there a way to save the rows displayed at the beginning and restore that at the end?

    Thanks,
    Tom

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    T,

    You could just add the following line to the end of your code:

    ActiveWindow.SmallScroll up:=17

    It will move your top row from 37 to 20

    HTH,
    Maud

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    THill (2014-11-12)

  4. #3
    New Lounger
    Join Date
    Dec 2013
    Posts
    22
    Thanks
    2
    Thanked 6 Times in 6 Posts
    Progress, but could I make a more general solution by combining the ActiveWindow.SmallScroll command with something that recorded the first row displayed when the macro starts to execute? That way presumably I could compute how far up or down to scroll each time. It wouldn't always be 17 up, and I've had the same issue with other spreasheets/macros in the past.

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Tom,

    Could you post your macro? My guess is that you're doing a lot of changing of the active cell? If you do most of the work using offsets from a given point and don't change the active cell you won't encounter the problem in the first place. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #5
    New Lounger
    Join Date
    Dec 2013
    Posts
    22
    Thanks
    2
    Thanked 6 Times in 6 Posts
    Maud gave enough to find an answer in my VBA book:

    ActiveWindow.ScrollRow returns the top row visible in the worksheet, so I can save that at the beginning, say to a variable TopRow. Then at the end after restoring the active cell, the final command is ActiveWindow.ScrollRow = TopRow.

    Thanks for your help. I had tried to figure it out on my own before posting, but didn't know what to search for in the book.

    Tom

Posting Permissions

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