We help IT Professionals succeed at work.

Active RowFunction in Excel 2007

I'm tyring to create a function that would always display the row of the active cell in the cell you place the funtion in. In other words, if I placed '=ShowRow()' in Cell A3, then the value in Cell A3 should always be the row of the active cell; everytime you click on a different cell, the value in Cell A3 should automatically update to indicate the row of the active cell.

I tried this code in VBA:

Function ShowRow()
ShowRow = ActiveCell.Row
End Function

Which works ok the first time you type it, but it doesn't update as you select different cells in the spreadsheet. Seems like there should be a standard Excel function to do this, but apparently not.
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010
Commented:
Hello,

this code will show the row of the current cell in A1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Range("A1") = Target.Row
End Sub

This code needs to go into the Sheet module (right-click the sheet tab  > View Code > Paste the code into the code window).

cheers, teylyn
CERTIFIED EXPERT
Commented:
Right-click on the sheet tab name
Select view code
Paste this code there

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A1").Value = ActiveCell.Row
End Sub

Author

Commented:
Both responses worked and I really appreciate the detailed instructions on how to apply it! Thank you!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.