Finding the last row

[SMILE]

Hello
Good morning!
I was trying to keep a history (in Col. D) of my data entered in a cell (A1)
It is working ok, but started recording from 2nd Row (D2) but I need to start it from the 1st row (D1).
Please help me to correct it. What i need is to keep the track from D1.
Thanks
Toms

Private Sub Worksheet_Change(ByVal Target As Range)

   If Not Intersect(Target, Range("A1")) Is Nothing Then
   confirm = MsgBox("Confirm ?", vbYesNo)
   If confirm = vbYes Then
    
    '    If Range("D65536").End(xlUp).Row = 1 Then
    '    lastrow = 1
    '    Else
 lastrow = Range("D65536").End(xlUp).Row + 1
    '     End If
     Cells(lastrow, 4).Value = Range("A1").Value
      
          End If
   Else
   End If
      
End Sub

[tom1977]

try this way

Private Sub Worksheet_Change(ByVal Target As Range)

   If Not Intersect(Target, Range("a1")) Is Nothing Then
   confirm = MsgBox("Confirm ?", vbYesNo)
   If confirm = vbYes Then
    
    
    lastrow = Cells(Rows.Count, "D").End(xlUp).Row + 1
    
    Range("d" & lastrow) = Target
          
    End If
   Else
   End If
      
End Sub

[TMS]

Maybe:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim awf As WorksheetFunction: Set awf = WorksheetFunction

If Not Intersect(Target, Range("A1")) Is Nothing Then
     confirm = MsgBox("Confirm ?", vbYesNo)
     If confirm = vbYes Then
     
         If awf.CountA(Columns(4)) = 0 Then
             lastrow = 1
         Else
             lastrow = Range("D65536").End(xlUp).Row + 1
         End If
        Application.EnableEvents = False
        Cells(lastrow, 4).Value = Range("A1").Value
        Application.EnableEvents = True
    End If
End If
      
End Sub

Regards

[SMILE]

Hi
@TM Shucks
It is working now. Thank you very much. But I couldn't figure it out what is wrong with my coding (considering the cancelled lines in my code). anyhow.. Thank you verymuch...
@Tom1977
Your code is same like mine in effect. It start recording from the 2nd Row. I wanted it to be from 1st row. TMShucks code is working perfect.

Thanks everyone..

[TMS]

If you leave in the commented code, it will always log to cell D1, as the End(xlUp).Row will equal 1 whether the cell is empty or not. If you comment the code out, it goes up to the last row and then adds 1. So, first time through it records in cell D2.

I've just checked to see if the column is empty and, if it is, set the next row to 1.

Note that, as you are making more changes to the sheet, you need to disable and re-enable events.

If this has answered your question, please mark your thread as solved.

Regards

[SMILE]

Thank u TM for the explanation!
Great!

[TMS]

You're welcome. Thanks for the rep.