MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Copying formula to top of a table which varies in size

Posted by Ben on October 26, 2000 12:16 PM

How can I use a macro to copy a formula in the last row of column G in a table, all the way up the table to the first row in column G, so the entire column in the table contains the same formula, but for the specific row.
e.g. =D7+D7 with the row above reading =C7+C8

But the catch is that the table varies in size from the first macro that will be run.

There are also more columns after column G!


Posted by Ivan Moala on October 27, 2000 2:46 AM

Assuming the top cell column is G1 then
the following 2 should work to fill the column/s
with your formula.....

Sub Autofill_1Column()

Dim lcell As String

lcell = Range("G1").End(xlDown).Address
Range(lcell).AutoFill Destination:=Range("G1:" & lcell), Type:=xlFillDefault

End Sub

Sub Autofill_MultiColumn()

Dim DataRg As String
Dim lcell As String
Dim Tcell As String

lcell = Range("G1").End(xlDown).Address
Tcell = Range(Range("G1").End(xlToRight), Range("G1").End(xlDown)).Address
DataRg = Range(Range(lcell), Range(lcell).End(xlToRight)).Address

Range(DataRg).AutoFill Destination:=Range(Tcell), Type:=xlFillDefault

End Sub