Excel’s inbuilt functions are powerful. They can achieve a lot. Almost anything can be conquered with these functions.
Recently needed a function to know which row is being modified. As far as i know, there’s no way this can be achieved by using excel’s function. This is where little bit of vba helps.
Here’s the code that helped.
Private Sub Worksheet_Change(ByVal Target As Range) If (Target.Column = 3 Or Target.Column = 4) Then If Target.Row > 1 Then Cells(1, 6) = Target.Row End If End Sub
In the above sheet change method, the code checks if any row has changed in the columns 3 and 4 and if something has changed beyond row 1 in these columns, the F1 cell is updated with the row number.
Simple and small code but very useful when one wants to know which row is getting modified in excel.
The information about the row being modified can be used to do lot of things using excel in built functions.
Now think where can one use this. Stay tuned I have a follow up post on the use.