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
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
    LVL 81

    Expert Comment

    by:zorvek (Kevin Jones)
    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

    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

    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.


    Author Comment

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

    Expert Comment

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

    Author Comment

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

    Author Comment

    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:
    ' Re: Insert Rows --   1997/09/24 Mark Hill <>
       ' 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( '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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    Title # Comments Views Activity
    how to pick files up from ftp via script 8 38
    Determine Range to Select 5 26
    Third Sunday of the Month 10 47
    Adding to a VBA? 6 27
    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    794 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now