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

Depot	WIP	Dept	Status	Date In
Aberdeen	22717	W	1	28/03/2018
Aberdeen	24105	W	1	27/04/2018
Aberdeen	24048	W	1	05/04/2018
Aberdeen	24330	W	1	05/04/2018

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

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