auto increment (auto_increment) in Excel with VBA (auto generate line numbers)

I was asked a question the other day, I'm still not quite sure why this was important to the user (see as Excel has row numbers provided automatically) and its also very easy to type the numbers and then drag down / fill to generate the next number, anyway the user had a large Excel document that they wanted to have automatic line numbers at various points (almost like a list or bullet points in Word)

I knocked up a quick macro that seems to do the job for the user

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column > 1 And Target.Column <= 7 Then
        If Target.Row - 1 > 0 Then
            Cells(Target.Row, 1).Value = Target.Worksheet.Cells(Target.Row - 1, 1) + 1
        End If
    End If
End Sub

No comments:

Post a Comment