VBA - AutoFill Series

[JamesT1973]

Hi all

Very new "VBAer"

I have Excel data as per below

	A	B	C
1	Location Code	Series Number	Country
2	4000	1	Australia
3	4001	2	Australia
4	4002		Australia
5	4010		Australia

<tbody>
</tbody>

I require this result

	A	B	C
1	Location Code	Series Number	Country
2	4000	1	Australia
3	4001	2	Australia
4	4002	3	Australia
5	4010	4	Australia

<tbody>
</tbody>

Can someone help with VBA code that will:
1) Copy down series in B1:B2
2) Copy down to last row in B, based on last value in Column A


Regards
James

 

[mohadin]

Hi
Try

Sub test()
    With [b2].Resize(Cells(Rows.Count, 1).End(xlUp).Row - 1)
        .Formula = "=row()-1"
        .Value = .Value
    End With
End Sub

 

[Fluff]

Another option

Sub JamesT1973()
   Range("B2:B3").AutoFill Range("B2", Range("A" & Rows.Count).End(xlUp).Offset(, 1)), xlFillSeries
End Sub