Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Macro to autofill formula to last row with data
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Macro to autofill formula to last row with data
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 On Fri, 22 Jan 2010 08:34:21 -0800, Joe M. wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Macro to autofill formula to last row with data
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" wrote: 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 On Fri, 22 Jan 2010 08:34:21 -0800, Joe M. wrote: 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. . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Macro to autofill formula to last row with data
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 On Fri, 22 Jan 2010 10:24:02 -0800, Joe M. wrote: 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" wrote: 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 On Fri, 22 Jan 2010 08:34:21 -0800, Joe M. wrote: 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. . |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Macro to autofill formula to last row with data
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 "Joe M." wrote: 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" wrote: 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 On Fri, 22 Jan 2010 08:34:21 -0800, Joe M. wrote: 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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofill in macro | Excel Discussion (Misc queries) | |||
autofill formula to every section of data | Excel Discussion (Misc queries) | |||
Formula to Autofill Info based on Other Data | Excel Discussion (Misc queries) | |||
Autofill macro | Excel Discussion (Misc queries) | |||
autofill macro | Excel Discussion (Misc queries) |