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.
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