Need Macro to autofill formula to last row with data

[Joe M.]

I have a worksheet which may contain data from A4 to A16. The first row A4
always contains data. I have formulas in B4 to L4. I need a macro to autofill
the formulas to the last row filled in col A4:A16. I have a chart that uses
these formulas so if I fill all the rows past the last filled row in col A
then the chart X axis expands. Therefore, if the formulas dont go past the
last filled row from A4 to A16 then the chart populates correctly. Can
someome tell me the macro for this?

Much appreciated.
Joe M.

 

[Gord Dibben]

Sub Auto_Fill()
Dim lRow As Long
With ActiveSheet
lRow = .Range("A" & Rows.Count).End(xlUp).Row
.Range("B4:L" & lRow).FillDown
End With
End Sub


Gord Dibben MS Excel MVP

 

[Joe M.]

Gord,
This works except it doesn't stop looking after A16 for the last filled
cell, Row 17 contains totals and A17 is filled with other data. So as it is,
it fills all the way to A17. The macro needs to look for the last row from A4
to A16.
Thanks again,
Joe M.

 

[Gord Dibben]

I misunderstood your reference to "last row".

A16 is not the last row in the column but it is last row in the range A4:A16

If you want to fill down to row 16 simply change the macro to

Sub Auto_Fill22()
Dim lRow As Long
With ActiveSheet
lRow = .Range("A16").Row
.Range("B4:L" & lRow).FillDown
End With
End Sub

Or possibly this which fills down to last row -1?

Sub Auto_Fill()
Dim lRow As Long
With ActiveSheet
lRow = .Range(("A" & Rows.Count)).End(xlUp).Offset(-1, 0).Row
.Range("B4:L" & lRow).FillDown
End With
End Sub


Gord

 

[JLatham]

A little change to Gord's code and it should work for you now. Gets kind of
strange looking because of the way that .End() works under conditions like
these.

Sub Auto_Fill()
Dim lRow As Long
With ActiveSheet
lRow = .Range("A4").End(xlDown).Row
If lRow > 16 Then
lRow = 16
End If

If .Range("A17").End(xlUp).Row = 4 _
And IsEmpty(.Range("A16")) Then
lRow = 4 ' nothing to fill, really
End If
.Range("B4:L" & lRow).FillDown
End With
End Sub