03/04/2013

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