VBA - AutoFill Series

[JamesT1973]

Hi all

Very new "VBAer"

I have Excel data as per below

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Location Code[/TD]
[TD]Series Number[/TD]
[TD]Country[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4000[/TD]
[TD]1[/TD]
[TD]Australia[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4001[/TD]
[TD]2[/TD]
[TD]Australia[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4002[/TD]
[TD][/TD]
[TD]Australia[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4010[/TD]
[TD][/TD]
[TD]Australia[/TD]
[/TR]
</tbody>[/TABLE]


I require this result

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Location Code[/TD]
[TD]Series Number[/TD]
[TD]Country[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4000[/TD]
[TD]1[/TD]
[TD]Australia[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4001[/TD]
[TD]2[/TD]
[TD]Australia[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4002[/TD]
[TD]3[/TD]
[TD]Australia[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4010[/TD]
[TD]4[/TD]
[TD]Australia[/TD]
[/TR]
</tbody>[/TABLE]

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