Returning Cell to Upper-Left-Most Corner

[Carroll]

Using VBA, I resort a range of cells by clicking on a column header.
After I have resorted based on a different column, the cells that are
visible are sometimes different. I was trying to find some code that
would record the upper-left-most cell into a variable, then return that
cell to the same upper-left-most corner, without changing the cell that
I have currently selected. Is this possible?

Thanks,

Carroll Rinehart

 

[Carroll]

One thing I want to add. I have the column headers frozen on row 6, so
that everything from row 7 on is scrollable. I don't know if this adds
to the complexity.

Carroll

 

[Tom Ogilvy]

Dim toprow as Long
toprow = ActiveWindows.Pane(2).ScrollRow.row


then

ActiveWindow.Pane(2).ScrollRow = toprow

 

[Carroll]

Thanks Tom. BTW, I am using Excel 2002.

After I have added the 3 lines of code, when it hits "toprow =
ActiveWindows.Pane(2).ScrollRow.Row", I get this error: "Run-time error
424. Object Required."

Carroll

 

[Tom Ogilvy]

Sorry, that was a typo I though I had corrected before posting. it should
be

toprow = ActiveWindows.Pane(2).ScrollRow

 

[Carroll]

Tom,

I'm still getting the same error after removing ".row" from the end.
Also, I notice that this line shows "ActiveWindows" and the last line
of code provided shows "ActiveWindow" (without the s). I tried it both
ways and got an error either way.

Carroll

 

[Tom Ogilvy]

There should be no s. Assume you have a hozizontal splitbar visible, here
is a demo from the immediate window:

? ActiveWindow.Panes.count
2
? activeWindow.Panes(2).ScrollRow
9
ActiveWindow.Panes(2).ScrollRow = 3
? activeWindow.Panes(2).ScrollRow
3

 

[Carroll]

Thanks Tom.