1

Using Excel VBA, how can I memorize and restore a worksheet to a precise set of visible rows and columns?

I have an excel macro that looks at the value of the current cell, computes a lot of stuff and runs around the whole workbook making changes, and then restores the cursor to the original cell. But the original cell ends up on the top row of the window, and that is annoying. I want the view of the sheet to remain the same, by somehow memorizing not only the original sheet and cell and .Selecting them afterward, but also by memorizing the exact set of visible rows and columns! How to?

This works:

Dim StartingSheet As String
Dim StartingCell As String
StartingCell = ActiveCell.Address
StartingSheet = ActiveSheet.Name
' do a lot of stuff
Worksheets(StartingSheet).Select
Range(StartingCell).Select

but by the end of the macro, the set of visible rows and columns shifts, which is bad.

I want to run the macro and NOT see any movement on the screen.

cybernetic.nomad
  • 6,100
  • 3
  • 18
  • 31
Bruce M
  • 119
  • 9
  • 4
    Are you using `Activate/select` in your macro? if yes, [you shouldn't](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Did you set `Application ScreenUpdating` to False? – cybernetic.nomad Feb 18 '19 at 18:31
  • 4
    Yep. Best way to restore the exact position is not to change it. – Comintern Feb 18 '19 at 18:32
  • Yes, I am using .Select. I'll have to study the link you pointed to, but will that fix my problem? Yes, I set ScreenUpdating to False. – Bruce M Feb 18 '19 at 18:39
  • 1
    Yes, if you don't change the active sheet or the selection, it's going to be the same at the end as when you started. – Comintern Feb 18 '19 at 18:51

2 Answers2

1

Thanks everybody. For my purposes, a good solution is:

    Dim StartingSheet As String
    Dim StartingCell As String
    Dim StartingScrollRow As Integer
    Dim StartingScrollColumn As Integer
    StartingCell = ActiveCell.Address
    StartingSheet = ActiveSheet.Name
    StartingScrollRow = ActiveWindow.ScrollRow  ' the row of the top left cell that is showing
    StartingScrollColumn = ActiveWindow.ScrollColumn ' the column of the top left cell showing

    ' do a bunch of stuff 

    Worksheets(StartingSheet).Select
    ActiveWindow.ScrollRow = StartingScrollRow  ' make this row the top-most row that’s showing
    ActiveWindow.ScrollColumn = StartingScrollColumn  ' make this col the left-most col showing
    Range(StartingCell).Select  ' select the cell that was originally selected
feetwet
  • 3,248
  • 7
  • 46
  • 84
Bruce M
  • 119
  • 9
  • Good solution. Note that you can `Dim StartingCell As Range` and then `Set StartingCell As Selection` in case the user has more than one cell selected. – feetwet Feb 06 '20 at 19:00
0

You can try this:

Set memorizevisiblerowsandcolumns=ActiveWindow.VisibleRange
'do stuff
memorizevisiblerowsandcolumns.Select
ActiveWindow.Zoom = True
Ad Huikeshoven
  • 51
  • 2
  • 6