Microsoft Excel - Automatically Extending Formulas

C

Cadillac Williams

Hi,

I have a large worksheet that has a number of fairly complex formulas that
are repeated in each row with only the row number reference changing. When I
insert a new row in between two existing rows that contain formulas I would
like excel to automatically insert the formulas I have in the row above it
into my new row. Excel already does this with my formatting, I want to know
how to do it with my formulas as well.
 
M

Michael

Hi Cadillac. If you copy the entire row, above the one where you are going
to insert a new row, and then paste insert row, the formulas will be copied
and the cell references will change. HTH
 
D

Don Guillett

How about if you put your formulas in row 1>hide the row>double click

right click sheet tab>view code>insert this. Now, when you double click
anywhere on the sheet row will be inserted before the last row and row 1
will be copied.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
lr = Cells(Rows.Count, "a").End(xlUp).Row
Rows(lr).Insert
Rows(1).Copy Rows(lr)
End Sub
 

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