CODEWITHSUNDEEP
vbaexcel
Hans Maggio
Hans Maggio

Reputation: 51

Exclude first row from macro

I have a macro that puts the current time into a cell upon editing any row. my problem is that this macro also executes for row 1 which are the titles. So it ends up changing the title of a column to a time.

The macro works fine but still changes the title. I tried the following: 

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False

    If ActiveCell.Row = 1 Then Exit Sub

    Cells(Target.Row, "I").Value = Now
    Application.EnableEvents = True

End Sub

Upvotes: 4

Views: 1910

Answers (2)

user4039065
user4039065

Reputation:

If you turn off event handling, provide error control that makes sure that events will be re-enabled.

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Safe_Exit
    Application.EnableEvents = False
    Dim r As Long, rw As Long, rng As Range, newTarget As Range
    For Each rng In Target
        If rng.Column <> 9 Then
            If newTarget Is Nothing Then
                Set newTarget = rng
            Else
                Set newTarget = Union(newTarget, rng)
            End If
        End If
    Next rng
    For r = 1 To newTarget.Rows.Count
        rw = newTarget.Rows(r).Row
        If rw > 1 Then _
            Cells(rw, "I").Value = Now
    Next r

Safe_Exit:
    Application.EnableEvents = True
End Sub

If you are pasting or filling a large number of values then Target is all of the cells that changed. You need to guard against the top row while everything else receives the timestamp. When Target is more than a single cell, you only want to timestamp once per row.

And you don't want to turn off event handling then exit without turning it back on again.

Upvotes: 2

Don Jewett
Don Jewett

Reputation: 1967

The ActiveCell can change to something else after you edit, so use the Target range rather than the ActiveCell. For example, if I hit {enter} to finish my edit, the ActiveCell is now on row 2 rather than 1.

Private Sub Worksheet_Change(ByVal Target As Range) 
    Application.EnableEvents = False

    With Target
        If .Row > 1 Then 
            Cells(.Row, "I").Value = Now 
        End If
    End With

    Application.EnableEvents = True

End Sub

I'm using With syntax to show the same Row you are comparing is the one you are editing. You could still put these on separate lines if you wish.

Also, user Jeeped makes a good point about the Application.EnableEvents = True line. It won't run if the row is 1, so they get turned off indefinitely. Better to test for > 1 and only run your update code on that condition.

Upvotes: 5

Related Questions