Macro to extend formulas

J

Joe M.

I am trying to create a macro to extend formulas in columns downward to the
last filled adjacent cells. i.e. Col A thru C are filled down to row 120. Col
D thru F Row 1 contains the formulas to be extended down to row 120. The
problem is that when I record a macro it refers to specific cells. The last
row filled in Cols A-C is dynamic but when I run the macro it extends the
formula down to the same place each time. I have read some posts that refer
to a "Relative Reference" option in the Stop Recording toolbar but I don't
have this. I am using Access 2003. Any suggestions are greatly appreciated!
Thanks,
Joe M.
 
G

Gord Dibben

Sub Auto_Fill()
Dim lRow As Long
With ActiveSheet
lRow = .Range("C" & Rows.Count).End(xlUp).Row
.Range("D1:F" & lRow).FillDown
End With
End Sub

Assumes you have pre-entered the formulas in D1:F1


Gord Dibben MS Excel MVP
 
R

Rick Rothstein

Assuming your current Column D formulas starts in Row 2 and is of this
structure...

=<formula>

why not change it to this...

=IF(A2="","",<formula>)

and the copy that down to a rows that is located as far down as you ever
think you might ever use, then do the same thing in Columns E and F for
their respective formulas. That way, you won't need to keep executing a
macro every time new data is added (the formulas will only display values
when there is an entry in Column A for that row).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top