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

HKDDSHH	31-Jan-13	HUS	8	8	8	0	-896.469	637.037	-636.555	-895.987	January	2013	Comp	-112.059	Thursday	Midweek
HKDDSHH	31-Jan-13	LBR	27	55	5	22	3398.06	869.801	1137.463	5405.324	January	2013	Transient	125.8541	Thursday	Midweek
HKDDSHH	31-Jan-13	LCR	9	16	2	7	812.4588	403.5741	92.46083	1308.494
HKDDSHH	31-Jan-13	LPP	0	0	0	0	0	0	0	0
HKDDSHH	31-Jan-13	SOV	3	6	0	3	110.8105	116.1265	105.4556	332.3927
HKDDSHH	31-Jan-13	Unknown	0	0	0	0	0	0	0	0

<tbody>
</tbody><colgroup><col><col><col span="15"></colgroup>

 

[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.