Get visible row and column number

Question

I have a simple macro at my Excel worksheet, which does some simple calculation. What happens is that for a better user experience I want the macro to run and when it is finished to return to the cell that the user was before he activated the macro, so that the user won't see any change on the screen.

Ok, so that's not to hard:

Dim activeRow As Integer
Dim activeCol As Integer

Sub myCode

    activeRow = ActiveCell.Row
    activeCol = ActiveCell.Column

    ' code...

    Cells(activeRow, activeCol).Select

End Sub

The problem with the code above is that the line and columns that were visible before the macro runs are not the same on the end, because the macro may have scrolled the worksheet to the left, right, up or down.

For example, you can see the cell E26 on your display either if the first line visible at your worksheet is 15 or 16.

So the question here is how can I know the line and column number that are visible at the user display?

I was wondering that would be something like:

Dim topLine As Integer
Dim bottomLine As Integer

topLine = ActiveWorksheet.GetVisibleGrid.TopLine
bottomLine = ActiveWorksheet.GetVisibleGrid.BottomLine

score 1 · Answer 1 · answered Nov 21 '15 at 10:35

Here is one approach. What I have done is to save the active cell. freeze panes there, then return to that cell at the end and unfreeze panes.

Dim cell As Range

    'save current cell and freeze panes here
    Set cell = ActiveCell
    ActiveWindow.FreezePanes = True

    'go somewhere way off screen
    Range("A1").Select

    'now go back and remove freeze panes
    cell.Select
    ActiveWindow.FreezePanes = False

score 1 · Accepted Answer · edited May 23 '17 at 11:46

re: "... for a better user experience I want the macro to run and when it is finished to return to the cell that the user was before he activated the macro, so that the user won't see any change on the screen."

The easiest way to do this is never use .Select or .Activate. There are less than an isolated handful of circumstances where using .Select is preferable to direct cell and worksheet reference(s) and only half again of those are actually necessary.

See How to avoid using Select in Excel VBA macros for methods on getting away from relying on .Select and .Activate to accomplish your goals.

Get started converted your code to eliminate .Select and .Activate. If you run into problems, post your working code here or at Code Review (Excel). If it is more than a page or so, post sections of it.

Glad to see you are onboard with getting away from .Select. Happy to help you with the conversion of any section you find difficult. — , Nov 21 '15 at 10:53
Would you mind checking this one? http://stackoverflow.com/questions/33843036/excel-vba-select-a-checkbox-from-another-worksheet — Danilo Setton, Nov 21 '15 at 11:57

Get visible row and column number

2 Answers2