Excel vba - bug when inserting several cells at the same time

Question

I need to assemble a table in which information will be inserted in columns A B and C, and the date and time of when it was modified will be inserted in column D I created a simple program q saves the moment of change, but when I insert several columns at once as shown in image 1, the offset plays the modifications for the neighboring columns, depending on the size of the selection block in copy and lap. I need to restrict the information to column D because in the future I will need to use the columns E F G for other functions, and I need q to be accepted to insert several columns at the same time, because I need to copy and paste a large number of rows for this new worksheet. Thank you.

Answer 1

I think I got something like what I wanted The "trick" is in the lock and unlock of the cells q will be modified, if I unlock only column D and keep the columns E F and G locked, when I glue a selection box of various cells, as image 2, offset will attempt to write a box of several cells but can only fill column D This is not ideal because it will generate error and an alert box will appear, I still have not found a way to select "order" automatically. Also I would like to create something that does not run depending on errors because I want to avoid corrupting the code, or generating loopings.

Answer 2

Alex, it’s best not to use Offset since the destination of Now() is fixed (column D). Note If i <= 10 before the Cells(i, 4).Value = Now(), which is not to add the result of Now() in all that is selected more than line 10, since one of its conditions is the Target.Row <= 10

Private Sub Worksheet_CHANGE(ByVal Target As Range)
    Dim i As Long
    Dim rowInicio As Long
    Dim numberOfRowSelected As Long

    rowInicio = Target.Row
    numberOfRowSelected = Selection.Rows.Count

    If Target.Column = 1 And Target.Row >= 1 And Target.Row <= 10 Then
    Application.EnableEvents = False
        For i = rowInicio To rowInicio + numberOfRowSelected - 1
            If i <= 10 Then Cells(i, 4).Value = Now()
        Next i
    End If
    Application.EnableEvents = True
End Sub

Answer 3

Edie good night, follows the first code, relatively simple. I hope it meets your expectation.

This first interpretation does not take into account a column insertion by selecting the entire column A. Since doing so it fills all column D with the date of the event, take the test.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 And Target.Row >= 1 And Target.Row <= 10 Then
        Application.EnableEvents = False
        Target.Offset(0, 3).Value = Now()
        Target.Offset(0, 4).Value = ""
        Application.EnableEvents = True
    End If
End Sub