Autonumber

[DarReNz]

Hi i have a column named Number and i wish to input autonumbers which will automatically add the number in each row starting from row A3 ? I want the autonumber to work when each row of value is input from another worksheet. how do i do this ? Thanks

Number
A3 1
A4 2
A5 3

[Cyberdude]

If you haven't done so already, call up the Excel Help facility, and do a search on "Autonumber". There may be some things in there that can get you started, especially about midway down the page where they talk about lists.

[malik641]

Hey DarReNz, welcome to the forums

Here's something simple to start you out...just something quick I typed up.

It works with copied and pasted cells, but it's a little slow because it's in a worksheet_change event, and everytime a number is placed under the "Number" column, the worksheet changes...repeating the macro too many times (at least this is my assumption).

[VBA]Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim tRows As Long
Dim i As Long

If Target(1, 1).Row < 3 Or Target(1, 1).Value = vbNullString Then Exit Sub
tRows = Target.Rows.Count

For i = 1 To tRows
cells(Target(i, 1).Row, 1).Value = Target(i, 1).Row - 2
Next i

End Sub[/VBA] Still workin' on it...

EDIT: Oh yeah, BTW, it only works with the fact that your "Number" column starts in A3...I'll work on that too.

[lucas]

Very nice solution Joseph

[malik641]

K, got it

Check it out:
[VBA]Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
Dim NumRow As Long
Dim tRows As Long
Dim i As Long
Dim cell As Variant

For Each cell In cells(1, 1).End(xlDown)
If cell.Value = "Number" Then
NumRow = cell.Row
Exit For
End If
Next cell

If Target(1, 1).Row <= NumRow Or Target(1, 1).Value = vbNullString Then
Application.EnableEvents = True
Exit Sub
End If

tRows = Target.Rows.Count

For i = 1 To tRows
cells(Target(i, 1).Row, 1).Value = Target(i, 1).Row - NumRow
Next i
Application.EnableEvents = True
End Sub[/VBA] Now this will work no matter where you have the "Number" field as long as it's in column A. So you could have this in "A6" or "A600" and it still counts correctly.

It won't delete the number though if you delete the values to the right of the number...unless you use this method:
[VBA]Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
Dim NumRow As Long
Dim tRows As Long
Dim i As Long
Dim cell As Variant

For Each cell In cells(1, 1).End(xlDown)
If cell.Value = "Number" Then
NumRow = cell.Row
Exit For
End If
Next cell

If Target(1, 1).Row <= NumRow Then
Application.EnableEvents = True
Exit Sub
End If

tRows = Target.Rows.Count
If Target(1, 1).Value = vbNullString Then
For i = 1 To tRows
cells(Target(i, 1).Row, 1).Clear
Next i
Application.EnableEvents = True
Exit Sub
End If

For i = 1 To tRows
cells(Target(i, 1).Row, 1).Value = Target(i, 1).Row - NumRow
Next i
Application.EnableEvents = True
End Sub[/VBA] Hope this helps