How do I add a new blank record (row)

[jayC]

I have a table that consists of one row for each record. Each row contains
columns for inputing data and columns containing formulas.

I am trying to create a macro that inserts a new row (record) ABOVE the last
row (record) so that the newest rows are at the top of the table.

Would like the new record created by the macro to be blank except for the
cells containing formulas.

I am easily able to create many records below or above the current record.
However, I do not wish to have many blank records above the latest record -
only one blank record at a time when I press a button.

My unsatisfactory solution to this problem* created another issue: A row
that contains the totals for each column keeps changing the row numbers
every time another row is inserted at the top of the table in row 3. When
creating a new number 3 row by insertion, the sum changes from
$C$3...$C$1000 to $C$4....$C$1001. If another row is inserted at number 3 it
changes to $C$5....$C$1002 and so on.

It does this even when an absolute sign($) is used in front of the cell
number.

Many Thanks

*My solution is to copy a row containing formulas but no data way down at
the bottom of the table in row 950 and inserting it at the top of the table.

 

[Roger Govier]

Hi

The following small macro will insert a new blank row at Row 3, and will
then copy the formulae from row 4 in C4:E4 to row 3. Adjust to suit

Sub InsertRow()
Rows("3:3").Insert Shift:=xlDown
Range("C4:E4").Copy Range("C3")
End Sub

As far as your totaling is concerned, one way would be to use an offset
formula
=SUM(OFFSET($C$2,1,0,997,1))

Since it is starting as an Offset from Cell C2, 1 it will always start from
C3 and the 997 will make it sum down to C1000
Adapt to suit.

 

[jtube]

Hi

The following small macro will insert a new blank row at Row 3, and will
then copy the formulae from row 4 in C4:E4 to row 3. Adjust to suit

Sub InsertRow()
Rows("3:3").Insert Shift:=xlDown
Range("C4:E4").Copy Range("C3")
End Sub

This works wonderfully.
Can you indicate what the code for the macro would look like if the
cells
containing formulas were in non-contiguous cells?
I have formula cells in columns E, I-M, O-S. The other cells are
input data
cells.

It has nothing to do with the macro, but for some reason the inserted
blank
cells are taking on the formatting of row 2, which I don t want (this
occurs
whenever I try to insert a row below row 2). The copied formula cells
are
formatted ok.

Many thanks.

As far as your totaling is concerned, one way would be to use an offset
formula
=SUM(OFFSET($C$2,1,0,997,1))

Since it is starting as an Offset from Cell C2, 1 it will always start from
C3 and the 997 will make it sum down to C1000
Adapt to suit.

This works beautifully

 

[Roger Govier]

Hi

Perhaps the better way around, would be to copy the row and just clear the
constants.
The following should keep the format of the inserted row the way you want.

Sub InsertRow()

Rows("3:3").Copy
Rows("3:3").Insert Shift:=xlDown
Rows("3:3").SpecialCells(xlCellTypeConstants, 23).ClearContents

End Sub