Tom’s Tutorials For Excel: Identifying Your Active Window’s Top Left Cell

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