Finding last Cell and Copying down formula in multiple colums

[argar418]

Good morning,

I have the following code which works but only fills down one row and I want it to fill down to the last row but I cannot work it out.

Dim Col As Range
For Each Col In Range("L:Q").Columns
With Cells(Rows.Count, Col.Column).End(xlUp)
.Offset(1).Formula = .Formula
.Resize(2).FillDown
End With
Next

Can someone tell me how to get it to copy down to the last cell in all the columns.

Thanks for your help. Andrew

[TABLE="width: 1099"]
<tbody>[TR]
[TD]HKDDSHH[/TD]
[TD="align: right"]31-Jan-13[/TD]
[TD]HUS[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-896.469[/TD]
[TD="align: right"]637.037[/TD]
[TD="align: right"]-636.555[/TD]
[TD="align: right"]-895.987[/TD]
[TD]January[/TD]
[TD]2013[/TD]
[TD]Comp[/TD]
[TD="align: right"]-112.059[/TD]
[TD]Thursday[/TD]
[TD]Midweek[/TD]
[/TR]
[TR]
[TD]HKDDSHH[/TD]
[TD="align: right"]31-Jan-13[/TD]
[TD]LBR[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]3398.06[/TD]
[TD="align: right"]869.801[/TD]
[TD="align: right"]1137.463[/TD]
[TD="align: right"]5405.324[/TD]
[TD]January[/TD]
[TD]2013[/TD]
[TD]Transient[/TD]
[TD="align: right"]125.8541[/TD]
[TD]Thursday[/TD]
[TD]Midweek[/TD]
[/TR]
[TR]
[TD]HKDDSHH[/TD]
[TD="align: right"]31-Jan-13[/TD]
[TD]LCR[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]812.4588[/TD]
[TD="align: right"]403.5741[/TD]
[TD="align: right"]92.46083[/TD]
[TD="align: right"]1308.494[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HKDDSHH[/TD]
[TD="align: right"]31-Jan-13[/TD]
[TD]LPP[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HKDDSHH[/TD]
[TD="align: right"]31-Jan-13[/TD]
[TD]SOV[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]110.8105[/TD]
[TD="align: right"]116.1265[/TD]
[TD="align: right"]105.4556[/TD]
[TD="align: right"]332.3927[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HKDDSHH[/TD]
[TD="align: right"]31-Jan-13[/TD]
[TD]Unknown[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col span="15"></colgroup>[/TABLE]

 

[MARK858]

Try on a copy of your data

Sub FillMe()
    Dim LstRw As Long
    LstRw = Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
    Range("L2:Q2").AutoFill Destination:=Range("L2:Q" & LstRw), Type:=xlFillDefault
End Sub

Edit: changed the ranges as I assume by the offset that you have headers

 

[argar418]

Hey Mark,

Thanks for the answer, the issue is the sheet has 30000+ rows in it and will grow, so it errors out when filling from the toplines.

I have worked out if I amend the .Resize bracket to say 250 it works down 250 rows but the data is not standard so I need to resize with a variable rather than a defined number. I think, if that is possible.

Dim Col As Range
For Each Col In Range("L:Q").Columns
With Cells(Rows.Count, Col.Column).End(xlUp)
.Offset(1).Formula = .Formula
.Resize(250).FillDown
End With
Next

 

[MARK858]

Why would it error out at filling 30000+ rows? I just filled 200000 rows with formula and got no error.

 

[argar418]

Hello Mark,

It was not working before as it showed an error but just tried again and it worked perfectly. Thank you so much.

 

[MARK858]

Your welcome, thanks for the feedback.