How do I Insert rows in excel while maintaining formulas from previous rows? Is there a way to Insert but copy previous row's formulas?

Posted on 2007-10-02
Medium Priority
Last Modified: 2010-05-18
We're using Excel 2003 and I have a simple budget sheet.

When my users enter their numbers, they sometimes insert their own rows (even though there are hundreds of rows available).

When this happens, the formulas get messed up.  Is there an easy way to have the previous row copied when a row is inserted?

If possible, no VBA answers as I don't know VBA (unless you can provide the code - which would be greatly appreciated)

Thank you for any information you can provide.  I have a budget meeting in 3 hours - Please help!
Question by:iquagmire
  • 4
  • 2
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 20000052
In tables where formulas reference cells above or below the row in which the formula resides, cell references in those formulas get mangled (set to #REF or changed to other cells in other rows) when inserting and deleting rows in the table. This happens because Excel always tries to maintain cell references when making changes to the structure of a worksheet. But this very functionality can lead to undesired results in a table that is frequently manipulated by inserting and deleting rows.

The most obvious solution to this problem is to not reference specific rows above or below the row in which the formula resides. If this is not possible then use the OFFSET function. For example, if a formula in row 6 references a cell in row 5, column A, replace the reference A5 with OFFSET(A6,-1,0). When formulas are constructed this way Excel will always leave the cell references alone when inserting and deleting rows.


Author Comment

ID: 20000184
This is good Kevin.  I will add that.

But, when a row is inserted it does not copy anything at all from the previous rows (or the one after it).  This causes a problem where I have 80 rows and someone is in line 20 and inserts a row and the cells in the new rows have no formula at all.

When a row is inserted, I need the previous formula to get copied into it so my bottom line is intact.
LVL 81

Accepted Solution

zorvek (Kevin Jones) earned 1500 total points
ID: 20000613
You might find this solution interesting:


Check it out and, if you like it, we can figure out how to adapt it to your worksheet.


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


Author Comment

ID: 20000926
This is great!  How can I do this?
LVL 10

Expert Comment

ID: 20000945
Dear iquagmire,
Would it not be easier to simply protect your spreadsheet against the inserting and deleting of rows and columns?

Author Comment

ID: 20001093
I actually WANT them to insert rows but I need the formulas to come with it.

Author Comment

ID: 20008449
I found the answer. But Zorvek came closest.  Thanks for the help!


Here's the code I copied and pasted:

Sub InsertRowsAndFillFormulas_caller()
  '-- this macro shows on Tools, Macro..., Macros (Alt+F8) dialog
  Call InsertRowsAndFillFormulas
End Sub
Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0)
' Documented:  http://www.mvps.org/dmcritchie/excel/insrtrow.htm
' Re: Insert Rows --   1997/09/24 Mark Hill <markhill@charm.net.noSpam>
   ' row selection based on active cell -- rev. 2000-09-02 David McRitchie
   Dim x as long
   ActiveCell.EntireRow.Select  'So you do not have to preselect entire row
   If vRows = 0 Then
    vRows = Application.InputBox(prompt:= _
      "How many rows do you want to add?", Title:="Add Rows", _
      Default:=1, Type:=1) 'Default for 1 row, type 1 is number
    If vRows = False Then Exit Sub
   End If

   'if you just want to add cells and not entire rows
   'then delete ".EntireRow" in the following line

   'rev. 2001-01-17 Gary L. Brown, programming, Grouped sheets
   Dim sht As Worksheet, shts() As String, i As Long
   ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
   i = 0
   For Each sht In _
    i = i + 1
    shts(i) = sht.Name

    x = Sheets(sht.name).UsedRange.Rows.Count 'lastcell fixup

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

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

    On Error Resume Next    'to handle no constants in range -- John McKee 2000/02/01
    ' to remove the non-formulas -- 1998/03/11 Bill Manville
    Selection.Offset(1).Resize(vRows).EntireRow. _
   Next sht
End Sub

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question