CODEWITHSUNDEEP
excelvba
Albin
Albin

Reputation: 1120

Viewing the lower right from a selected Range in Excel via VBA

If you select a "large" range via VBA (e.g. range.Select) which is bigger then the screen size and bring the window/worksheet to the front (e.g. workbook.Activate) you will see the top left part of the selected range and whatever else fits on the screen.

Is it possible to show "the opposite" side, the lower right + whatever fits on the screen?

I thought about using something like

ActiveWindow.ScrollRow = tmpLastRowNr
ActiveWindow.ScrollColumn = tmpLastColumnNr

But then I will need some kind of solution to determine how much of the table to the upper left of it and set this as an offset... I found a way to set/check the windowsize with Application.Height etc. but I actually need the size of the "table area". Did I miss a command or is this just not possible to do it "directly" (without calculating the visible area "manually" similar to this)?

Upvotes: 0

Views: 462

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

Try the next code, please:

Sub testLastVisColRow()
  Dim LastVisCol As Long, lastVisRow As Long, selLastRow As Long, selLastCol As Long
  Range("A2:Z32").Select 'use here any range...
    lastVisRow = ActiveWindow.VisibleRange.Rows(ActiveWindow.VisibleRange.Rows.count).Row
    LastVisCol = ActiveWindow.VisibleRange.Columns(ActiveWindow.VisibleRange.Columns.count).Column

    selLastRow = Selection.Rows(Selection.Rows.count).Row
    selLastCol = Selection.Columns(Selection.Columns.count).Column

    ActiveWindow.SmallScroll Down:=selLastRow - lastVisRow + 1
    ActiveWindow.SmallScroll ToRight:=selLastCol - LastVisCol + 1

    If selLastRow > lastVisRow Or selLastCol > LastVisCol Then
        ActiveWindow.SmallScroll Down:=selLastRow - lastVisRow + 1
        ActiveWindow.SmallScroll ToRight:=selLastCol - LastVisCol + 1
        cells(selLastRow, selLastCol).Activate 'in order to perfectly simulate the reverse of the normal behavior :)
    End If
End Sub

Upvotes: 1

Related Questions