Copy Row Of Formulas After Last Used Row?

[M. Johnson]

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

 

[Tom Ogilvy]

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.

 

[M. Johnson]

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]

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