Fill formulas down in macro
 

I'd like to create a macro that fills a formula down to the final row that
has any data.

-- Just filling to adjacent cells (e.g. double-clicking on the autofill
button) doesn't work because some of the rows don't have data in the cells
the formula is looking for.
-- I know I can select all the cells below the one I entered the formula in
and hit ctrl-D to fill the formula, but I need this macro to be able to run
no matter how many rows of data are present.
-- I tried filling the fomula all the way past the highest row number I'd
ever thought I'd reach but then (because the formula includes absolute text),
it includes all those blank rows (with just the one bit of absolute text) in
the default print area.

It's not a huge deal to just set the print area after running the macro, I
know, but I'm creating this for the use of many people so I'm trying to make
it as "one-step" as possible. Any ideas?

Thanks!
HH

You would use something like:

Dim iLastRow As Long
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("B1:B" & iLastRow).Filldown

This code identifies the last row (iLastRow) in col. A
with data in it, then copies the formula down in cell B1
to that last row.

HTH
Jason
Atlanta, GA

-----Original Message-----
I'd like to create a macro that fills a formula down to
the final row that
has any data.

-- Just filling to adjacent cells (e.g. double-clicking
on the autofill
button) doesn't work because some of the rows don't have
data in the cells
the formula is looking for.
-- I know I can select all the cells below the one I
entered the formula in
and hit ctrl-D to fill the formula, but I need this
macro to be able to run
no matter how many rows of data are present.
-- I tried filling the fomula all the way past the
highest row number I'd
ever thought I'd reach but then (because the formula
includes absolute text),
it includes all those blank rows (with just the one bit
of absolute text) in
the default print area.

It's not a huge deal to just set the print area after
running the macro, I
know, but I'm creating this for the use of many people
so I'm trying to make
it as "one-step" as possible. Any ideas?

Thanks!
HH
.

Thanks! It's giving me an error, but you've definitely pointed me in the
right direction. I think I'll be able to get it squared away from here.

"Jason Morin" wrote:

You would use something like:

Dim iLastRow As Long
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("B1:B" & iLastRow).Filldown

This code identifies the last row (iLastRow) in col. A
with data in it, then copies the formula down in cell B1
to that last row.

HTH
Jason
Atlanta, GA

-----Original Message-----
I'd like to create a macro that fills a formula down to
the final row that
has any data.

-- Just filling to adjacent cells (e.g. double-clicking
on the autofill
button) doesn't work because some of the rows don't have
data in the cells
the formula is looking for.
-- I know I can select all the cells below the one I
entered the formula in
and hit ctrl-D to fill the formula, but I need this
macro to be able to run
no matter how many rows of data are present.
-- I tried filling the fomula all the way past the
highest row number I'd
ever thought I'd reach but then (because the formula
includes absolute text),
it includes all those blank rows (with just the one bit
of absolute text) in
the default print area.

It's not a huge deal to just set the print area after
running the macro, I
know, but I'm creating this for the use of many people
so I'm trying to make
it as "one-step" as possible. Any ideas?

Thanks!
HH
.