Tom’s Tutorials For Excel: Identifying Your Active Window’s Top Left Cell
Here are a few macros that identify the top left cell of your active window’s spreadsheet.
The first example relates to this picture, where cell HX63524 happens to be the top left cell in the active window. The macro displays the cell’s address in absolute and relative fashion.
Sub TopLeftCellAddress() With ActiveWindow MsgBox _ "Absolute ref: " & Cells(.ScrollRow, .ScrollColumn).Address & vbCrLf & _ "Relative ref: " & Cells(.ScrollRow, .ScrollColumn).Address(0, 0), , _ "Top left cell in window..." End With End Sub
If you want to place a value in the cell that is 1 row below and 5 columns to the right of whatever the active window’s top left cell might be, here’s how:
Sub TopLeftCellOffset() With ActiveWindow Cells(.ScrollRow, .ScrollColumn).Offset(1, 5).Value = "Hello" End With End Sub
Finally, suppose you want to know what the top left cell would be on another worksheet (other than the worksheet you are currently on), if and when you activate that other worksheet. For example, suppose you are currently working on some sheet other than Sheet1, but you want to place into cell A1 of Sheet2 whatever value is in the cell on Sheet1 that would be in the top left corner if you were to activate Sheet1. This macro would accomplish that:
Sub TopLeftCellOtherSheet() Application.ScreenUpdating = False Dim strVal As String Worksheets("Sheet1").Activate With ActiveWindow strVal = Cells(.ScrollRow, .ScrollColumn).Value End With With Worksheets("Sheet2") .Activate 'Optional .Range("A1").Value = strVal End With Application.ScreenUpdating = True End Sub
Another way to address the top left cell on the active worksheet is to use the ActiveWindow’s VisibleRange property…
MsgBox ActiveWindow.VisibleRange(1).Address
ActiveWindow.VisibleRange(1).Value = SomeValue
Thank you, this works in xl2010