Automatically Fill Columns (Date and Time) with VBA

Hi Community, I'd like to fix a problem I'm having when trying to run some code in vba that helps me fill a date column and another column with time. Used code: Private Sub Worksheet_Cha...

excel

Macros and VBA

amit_bhola

Mar 02, 2022

naesio_maia , I assume that you are trying to insert a blank row, and that generates the error. If so, then please change the code to as given below. This allows blank row(s) to be inserted :-

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo Handler
    If Target.Columns.Count = 1 Then
        If Target.Column = 2 And Target.Value <> "" Then
        Application.EnableEvents = False
        Target.Offset(0, 1) = Format(Now(), "dd/mm/yyyy")
        Application.EnableEvents = True
        End If
    End If
Handler:

On Error GoTo Handler2
    If Target.Columns.Count = 1 Then
        If Target.Column = 2 And Target.Value <> "" Then
        Application.EnableEvents = False
        Target.Offset(0, 2) = Format(Now(), "hh:mm AM/PM")
        Application.EnableEvents = True
        End If
    End If
Handler2:

End Sub

amit_bhola

Iron Contributor

Mar 02, 2022

naesio_maia , I assume that you are trying to insert a blank row, and that generates the error. If so, then please change the code to as given below. This allows blank row(s) to be inserted :-

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo Handler
    If Target.Columns.Count = 1 Then
        If Target.Column = 2 And Target.Value <> "" Then
        Application.EnableEvents = False
        Target.Offset(0, 1) = Format(Now(), "dd/mm/yyyy")
        Application.EnableEvents = True
        End If
    End If
Handler:

On Error GoTo Handler2
    If Target.Columns.Count = 1 Then
        If Target.Column = 2 And Target.Value <> "" Then
        Application.EnableEvents = False
        Target.Offset(0, 2) = Format(Now(), "hh:mm AM/PM")
        Application.EnableEvents = True
        End If
    End If
Handler2:

End Sub

naesio_maia
Copper Contributor
Mar 02, 2022
I was wondering if I could add one more condition to have the Target. Column since I understand that the Target. Column is determining which column the information will enter.

Forum Discussion

Automatically Fill Columns (Date and Time) with VBA

Resources