CODEWITHSUNDEEP
excelvba
Danilo Setton
Danilo Setton

Reputation: 713

Get visible row and column number

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

Upvotes: 1

Views: 294

Answers (2)

user4039065
user4039065

Reputation:

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.

Upvotes: 1

Bob Phillips
Bob Phillips

Reputation: 437

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

Upvotes: 1

Related Questions