CODEWITHSUNDEEP
vbaexcelrow
Leon Wurr
Leon Wurr

Reputation: 67

Last active cell row

I'm having difficulties with the "ActiveCell.Row" command on a VBA code, I know it must be a simple fix, but I've done some research and didn't find an answer to my problem.

The code bellow is getting the row number of the next active cell, and not the cell that I've edited.

Example: If I edit the cell "B2" and press enter, it returns the row of B3, because that will be the current active cell when the code runs. If I press tab, it returns the right number which is "2" because the active cell will be "C2". And if I press the up-arrow it returns the row "1", because the active cell will be "B1".

I appreciate your help! Thanks! (P.S. I've tried the solutions given here and some others I've found, but without success)

Private Sub Worksheet_Change(ByVal Alvo As Range)
    Dim limite_maximo As Integer
    limite_maximo = 1000
    If Alvo.Cells.Count > 1 Or IsEmpty(Alvo) Then Exit Sub

    If Alvo.Column = 2 And Alvo.Row >= 1 And Alvo.Row <= limite_maximo Then
        Application.EnableEvents = False
        a = ActiveCell.Row - 5
        b = 10
        Alvo.Offset(0, 2).Value = b
        Alvo.Offset(0, 3).Value = a
        Alvo.Offset(0, 4).Value = b & "." & Format(a, "000000")
        Application.EnableEvents = True
    End If

End Sub

Upvotes: 1

Views: 1040

Answers (2)

Leon Wurr
Leon Wurr

Reputation: 67

Got it thanks to the comments of Paul Kelly and Rory

I've changed the line "a = ActiveCell.Row - 5" to "a = Alvo.Row - 5" and now it works perfectly! Sorry for the newbie question, but I was really struggling with that one.

Thanks!

Upvotes: 0

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

In my Excel Options, the cursor moves down after entering data in a cell.
If I install:

Private Sub Worksheet_Change(ByVal Target As Range)
    MsgBox Target.Row
    MsgBox ActiveCell.Row
End Sub

and edit cell B9, the first output will be 9 but the second output will be 10.

This way you can determine the row number of the cell that was changed and the row number of the active cell after the change occurred.

Upvotes: 1

Related Questions