Macro Help

[MartinSpellacy]

Hi,

I have the below table which has 5 colums in it. The data in columns B to D is already populated for me however the only data in column A is found in Cell A2. I have created a macro to auto fill column A ( please see below). In my example I have 236 rows to autofil.

My problem is this.... my row range is dynamic and I may have less or more rows. How to I edit the macro so that it autofills to the potential last row?

Thanks in advnace



[TABLE="width: 345"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Depot [/TD]
[TD] WIP [/TD]
[TD]Dept[/TD]
[TD]Status[/TD]
[TD]Date In[/TD]
[/TR]
[TR]
[TD="align: center"]Aberdeen[/TD]
[TD="align: center"]22717[/TD]
[TD="align: center"] W[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]28/03/2018[/TD]
[/TR]
[TR]
[TD="align: center"]Aberdeen[/TD]
[TD="align: center"]24105 [/TD]
[TD="align: center"] W[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]27/04/2018[/TD]
[/TR]
[TR]
[TD="align: center"]Aberdeen[/TD]
[TD="align: center"]24048[/TD]
[TD="align: center"] W[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]05/04/2018[/TD]
[/TR]
[TR]
[TD="align: center"]Aberdeen[/TD]
[TD="align: center"]24330[/TD]
[TD="align: center"] W[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]05/04/2018[/TD]
[/TR]
</tbody>[/TABLE]



Sheets("Ab").Select
Range("A2").Select
Selection.autofill Destination:=Range("A2:A236")
Range("A2:A236").Select

 

[Joe4]

Welcome to the Board!

We can use column B to dynamically find the last row, and then autofill, i.e.

Dim lr as Long
lr = Cells(Rows.Count,"B").End(xlUp).Row
[COLOR=#333333]Range("A2").A[/COLOR][COLOR=#333333]utofill Destination:=Range("A2:A" & lr)[/COLOR]

 

[oldbrewer]

in a helper cell count the non blank rows, say it is in AA1 then loop the macro from 2 to cells(1,27)

 

[MartinSpellacy]

HI Joe

Thanks for this. I am a bit of noob when it comes to VBA.

What should my final VBA code look like then?

 

[Joe4]

What I gave you is all you need.
Replace what you have now (and posted originally) with that (within your procedure), i.e.

Sub MyAutoFill()

    Dim lr As Long
    
    lr = Cells(Rows.Count, "B").End(xlUp).Row
    Range("A2").AutoFill Destination:=Range("A2:A" & lr)

End Sub