Copy Row Of Formulas After Last Used Row?

Hello all. I've seen this done before, but have no idea how to do it, or
what it is even called.

I have a worksheet with Row 1 containing headers, and Row 2 containing the
formulas I want to use for subsequent rows (it is just these two lines for
now, but will grow over the next few months). Some cells in Row 2 have
formulas, but most do not. Is there a way that I can automatically copy
those formulas to the next empty row if the current last row has data in it?
I realize that I can just select the cells and drag the handle down far
enough to cover myself, but what I'm trying to avoid is having hundreds of
lines of formulas that aren't doing anything (thus increasing file size).

Thanks in advance

Turn on the macro recorder and do it manually. This should give you the
basic code. Then you can read help about the methods recorded and see how
to adjust it to do what you want.

--
Regards,
Tom Ogilvy

"M. Johnson" <[email protected]> wrote in message
news:[email protected]...
> Hello all. I've seen this done before, but have no idea how to do it, or
> what it is even called.
>
> I have a worksheet with Row 1 containing headers, and Row 2 containing the
> formulas I want to use for subsequent rows (it is just these two lines for
> now, but will grow over the next few months). Some cells in Row 2 have
> formulas, but most do not. Is there a way that I can automatically copy
> those formulas to the next empty row if the current last row has data in
it?
> I realize that I can just select the cells and drag the handle down far
> enough to cover myself, but what I'm trying to avoid is having hundreds of
> lines of formulas that aren't doing anything (thus increasing file size).
>
> Thanks in advance
>
>

Funny, I started doing this just as you responded. And I succeeded, except
that I have to call my new macro at each new line. This is faster than it
was, but it would be nice if it could be called automatically. Is there a
way to do this? Say, some sort of conditional statement (I don't know VBA,
so this is plain english) where A3 is the active cell:

if activecell.range("A1")="" and activecell.offset(-1,0).range("A1")>0
then run x macro
else do nothing

Just curious. Like I said in the first post, I've seen this done before (a
list that had, say, 15 lines because 90% of the time, that would be enough,
but in that 10%, it would add more lines as needed...if all that makes any
sense). Thanks again.


"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> Turn on the macro recorder and do it manually. This should give you the
> basic code. Then you can read help about the methods recorded and see how
> to adjust it to do what you want.
>
> --
> Regards,
> Tom Ogilvy
>
> "M. Johnson" <[email protected]> wrote in message
> news:[email protected]...
> > Hello all. I've seen this done before, but have no idea how to do it,
or
> > what it is even called.
> >
> > I have a worksheet with Row 1 containing headers, and Row 2 containing
the
> > formulas I want to use for subsequent rows (it is just these two lines
for
> > now, but will grow over the next few months). Some cells in Row 2 have
> > formulas, but most do not. Is there a way that I can automatically copy
> > those formulas to the next empty row if the current last row has data in
> it?
> > I realize that I can just select the cells and drag the handle down far
> > enough to cover myself, but what I'm trying to avoid is having hundreds
of
> > lines of formulas that aren't doing anything (thus increasing file
size).
> >
> > Thanks in advance
> >
> >
>
>

Automatically means something has to trigger the action. Excel provides
events which fire when certain activities take place. For the worksheet
some of the key events are Calculate, SelectionChange, Change.

You could put your code in one of these events. In the event code, you
would check how many rows with formula have not been filled in yet and when
it goes below a certain level, you could add rows.

See Chip Pearson's page on events as a start.

http://www.cpearson.com/excel/events.htm


--
Regards,
Tom Ogilvy


"M. Johnson" <[email protected]> wrote in message
news:[email protected]...
> Funny, I started doing this just as you responded. And I succeeded,
except
> that I have to call my new macro at each new line. This is faster than it
> was, but it would be nice if it could be called automatically. Is there a
> way to do this? Say, some sort of conditional statement (I don't know
VBA,
> so this is plain english) where A3 is the active cell:
>
> if activecell.range("A1")="" and activecell.offset(-1,0).range("A1")>0
> then run x macro
> else do nothing
>
> Just curious. Like I said in the first post, I've seen this done before
(a
> list that had, say, 15 lines because 90% of the time, that would be
enough,
> but in that 10%, it would add more lines as needed...if all that makes any
> sense). Thanks again.
>
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > Turn on the macro recorder and do it manually. This should give you the
> > basic code. Then you can read help about the methods recorded and see
how
> > to adjust it to do what you want.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "M. Johnson" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hello all. I've seen this done before, but have no idea how to do it,
> or
> > > what it is even called.
> > >
> > > I have a worksheet with Row 1 containing headers, and Row 2 containing
> the
> > > formulas I want to use for subsequent rows (it is just these two lines
> for
> > > now, but will grow over the next few months). Some cells in Row 2 have
> > > formulas, but most do not. Is there a way that I can automatically
copy
> > > those formulas to the next empty row if the current last row has data
in
> > it?
> > > I realize that I can just select the cells and drag the handle down
far
> > > enough to cover myself, but what I'm trying to avoid is having
hundreds
> of
> > > lines of formulas that aren't doing anything (thus increasing file
> size).
> > >
> > > Thanks in advance
> > >
> > >
> >
> >
>
>