Inserting new rows with mixed formulas and data w/o copying data

DMcRitchie

unread,

Mar 11, 1998, 3:00:00 AM3/11/98

to

I would like to insert rows into my spreadsheet and fixup the formulas that
might otherwise be destroyed. I do not want to copy cells with data. I only
want to copy the formulas. If it is not a formula leave or make the cell
null.

I am pretty sure that the SOLUTION WILL REQUIRE CHECKING CELLS TO SEE IF
INSERTED CELL
IS BELOW A FORMULA OR NOT. Please keep in mind that a generic solution is
wanted not a test to match this particular example which only has formulas in
one column (col E).

Mark Hill <mark...@charm.net.noSpam> proposed a solution to a problem
sometime back that I will use to illustrate what I want and don't want.

Representation of Original Data
--C-- --D-- --E-- Formula in E
13 0 13 =C2-D2
0 14 -1 =E2+C3-D3 <--- Select this row insert 3 lines

25 0 24 =E3+C4-D4
37 0 61 =E4+C5-D5
38 0 99 =E5+C6-D6 0's would normally be null cells.

Mark's Macro: (9 non blank lines)

Sub InsertRowsAndFillFormulas()
' Re: Insert Rows -- 1997/09/24 Mark Hill <mark...@charm.net.noSpam>
Dim vRows%
vRows = Application.InputBox(prompt:="How many rows do you want to add?",
Title:="Add Rows", default:=1, Type:=1) 'type 1 is number

If vRows = False Then Exit Sub
'if you just want to add cells and not entire rows then delete ".EntireRow"
in the following line

Selection.Resize(rowsize:=2).Rows(2).EntireRow.Resize(rowsize:=vRows).Insert
shift:=xlDown

Selection.AutoFill Selection.Resize(rowsize:=vRows + 1), xlFillDefault
End Sub

Results:
--C-- --D-- --E-- Formula in E
13 0 13 =C2-D2
0 14 -1 =E2+C3-D3
-----------------------------------------------------
1 15 -15 =E3+C4-D4 Three inserted lines
2 16 -29 =E4+C5-D5
3 17 -43 =E5+C6-D6
-------------------------------------------------------
25 0 24 =E3+C7-D7 <-- note formula incorrect.
37 0 61 =E7+C8-D8
38 0 99 =E8+C9-D9

This is what I want "--" represents null cells
--C-- --D-- --E-- Formula in E
13 0 13 =C2-D2
0 14 -1 =E2+C3-D3
-----------------------------------------------------
-- -- -- =E3+C4-D4 Three insert lines
-- -- -- =E4+C5-D5 "--" are null cells
-- -- -- =E5+C6-D6
-------------------------------------------------------
25 0 24 =E6+C7-D7 <- formula is "corrected"
37 0 61 =E7+C8-D8 (as if vRows + 2)
38 0 99 =E8+C9-D9

After inserting lines the results should show inserted cells below non-formulas
to be empty, and below formulas to have an inserted formula. The formula must
be extended one row additionally to fix the original formula below the inserted
lines.

One more comment. The solution might be more Excel like if the rows were
selected BELOW the insertion point, in this case select three rows below the
insert point. Just a thought. This eliminates the request for number of rows
to insert and makes it easy to install macro on a toolbar button. Disadvantage
if line below is unlike the lines above as a cell contain totals =SUM(....

Column F in my example uses a macro to display column E.
Should make testing easier.

Function GetFormula(Cell)
'Documented in http://members.aol.com/dmcritchie/excel/formula.htm

GetFormula = Cell.Formula
End Function

I hope this is clear

TIA,
// David McRitchie DMcRi...@aol.com

Bill Manville

unread,

Mar 11, 1998, 3:00:00 AM3/11/98

to

' to remove the non-formulas

Selection.Offset(1).Resize(vRows).EntireRow.SpecialCells(xlConstants).ClearContents

If you wrote the formula in E3 as
=OFFSET(E3,-1,0)+C3-D3
and copied it down, you would not need to correct the formula in the
row after the inserted rows.

Bill Manville
Oxford, England
Microsoft Excel - MVP

DMcRitchie

unread,

Mar 12, 1998, 3:00:00 AM3/12/98

to

That was a quick reply Bill,

It certainly works and I picked up how to code the spreadsheet in a manner more
conducive to adding and deleting rows.

I think I would still like to have a coding though that would work on my
simple formulas so that it would be more "foolproof" in invoking the macro.
But the point is well taken that the coding of the spreadsheet could be easily
changed.

using your suggested
=OFFSET(E3,-1,0)+C3-D3
instead of
=E2+C3-D3

so that all the parts name cells on the same row so that the row can copied
down without correcting a formula below the inserted lines afterwards.

I was hoping to learn how to code and test some of these things in Visual Basic
--- you make it seem so simple with your shortcut. :-)

I guess I did better in explaining what I wanted this week. Thanks again.

Thanks,
David McRitchie DMcRi...@aol.com

Brent Miller

unread,

Mar 13, 1998, 3:00:00 AM3/13/98

to

David:

Here is a subroutine that might do what you want. I agree that inserting
above the selection is more like the normal Excel Insert Rows function,
and that is how this routine works. Select an area that marks the rows
to be inserted and the columns that have formulas or data to copy.

This routine does little checking, and assumes that the formulas on the
row above the selection are what you want in all of the inserted rows.

'=============================================
' Insert rows in selection and copy formulas from the row above.
' This subroutine will fix up formula references in the row below
' the inserted rows that should refer to the previous row. It will
' NOT handle references to any other rows.
'=============================================
Sub InsertRows()

Dim R As Range
Dim C As Object
Dim i As Long
Dim NumRows As Long
Dim SavedRow As Variant

With Selection
NumRows = .Rows.Count
' Save formulas and data for top row of the selection
' (saves value for cells that don't have formulas)
' SavedRow will be an array with two dimensions, 1 row
' by however many columns are selected
SavedRow = .Resize(RowSize:=1).Formula
' Insert new rows
.EntireRow.Insert
End With

' Save the selected range - PasteSpecial will modify it
Set R = Selection

' Copy formulas and data from row above selection
R.Offset(-1, 0).Resize(RowSize:=1).Copy

' Paste formulas in inserted rows and row below where
' formula reference will have been corrupted. Data in
' the last row will be restored later
R.Resize(RowSize:=NumRows + 1).PasteSpecial

' Clear data and leave formulas in inserted rows
' (but NOT in the row below the inserted rows!)
For Each C In R
If Left(C.Formula, 1) <> "=" Then
C.Formula = Empty
End If
Next C

' Restore data (but not formulas!) to the row below the
' inserted rows
With R.Offset(NumRows, 0)
For i = 1 To UBound(SavedRow, 2)
If Left(SavedRow(1, i), 1) <> "=" Then
.Cells(1, i).Formula = SavedRow(1, i)
End If
Next i
End With

' Restore selection and clear copy mode marker

R.Select
Application.CutCopyMode = False

End Sub

In article <19980311213...@ladder03.news.aol.com>, dmcri...@aol.com

(DMcRitchie) wrote:
>I would like to insert rows into my spreadsheet and fixup the formulas that
>might otherwise be destroyed. I do not want to copy cells with data. I only
>want to copy the formulas. If it is not a formula leave or make the cell
>null.
>

<< Lines deleted from original post >>

>Representation of Original Data
>--C-- --D-- --E-- Formula in E
>13 0 13 =C2-D2
>0 14 -1 =E2+C3-D3 <--- Select this row insert 3 lines
>
>25 0 24 =E3+C4-D4
>37 0 61 =E4+C5-D5
>38 0 99 =E5+C6-D6 0's would normally be null cells.
>

<< Lines deleted from original post >>

>This is what I want "--" represents null cells
>--C-- --D-- --E-- Formula in E
>13 0 13 =C2-D2
>0 14 -1 =E2+C3-D3
>-----------------------------------------------------
>-- -- -- =E3+C4-D4 Three insert lines
>-- -- -- =E4+C5-D5 "--" are null cells
>-- -- -- =E5+C6-D6
>-------------------------------------------------------
>25 0 24 =E6+C7-D7 <- formula is "corrected"
>37 0 61 =E7+C8-D8 (as if vRows + 2)
>38 0 99 =E8+C9-D9
>
>After inserting lines the results should show inserted cells below non-formulas
>to be empty, and below formulas to have an inserted formula. The formula must
>be extended one row additionally to fix the original formula below the inserted
>lines.
>

>One more comment. The solution might be more Excel like if the rows were
>selected BELOW the insertion point, in this case select three rows below the
>insert point. Just a thought. This eliminates the request for number of rows
>to insert and makes it easy to install macro on a toolbar button. Disadvantage
>if line below is unlike the lines above as a cell contain totals =SUM(....
>

<< Lines deleted from original post >>

>
>TIA,
>// David McRitchie DMcRi...@aol.com

Brent Miller bmi...@scruznet.com