Excel/VBA Automatic Numbering

  • Thread starter Thread starter manpreet.khera
  • Start date Start date
M

manpreet.khera

Hey,

I am trying to use a macro to get my excel worksheet to number
automatically (the rows)....however when i insert a row, the number
tracking is off and there is no automatic numbering...

This is the code I am using:

Sub Autonumber()
done = 0
X = 2
Do While done < 1
If (Cells(X, 1) = "") Then
entry = X - 1
done = 1
Else
X = X + 1
End If
Loop

Cells(X, 1) = entry
X = entry + 1


End Sub

Can anyone please help me ASAP!!!

THANKS :)

Munny Khera
 
Try putting this code into the Worksheet's code area:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Rows.Count = 1 And _
Target.Address = "$" & Target.Row & ":$" & Target.Row Then
Range("A" & Target.Row).Formula = "=Row()-1"
End If

End Sub

To get to the proper place to put the code, right-click on the worksheet's
tab and choose [View Code] from the list. Copy the above code and paste it
in. Repeat for all sheets you need this function on.

What it does - it tests to see if you've inserted a row by first asking if
the count of rows in the current selection (Target) is one AND if that is
true, by seeing if the address returned for the Target looks like a row
address, $6:$6 for example if you'd just inserted a new row 6.

The formula =ROW()-1
returns the row number -1, so at row 6 it would display 5. I presume that's
what you wanted based on what I saw in your code.

Hope this helps some.
 
Back
Top