How do I add rows to a spreadsheet and keep t

Microsoft Excel 2003 (full product)
November 18, 2010 at 10:42:07
Specs: Windows 2003, unknown
How do I add rows to a spreadsheet so that the existing formulas copy into the new rows? I want the emply cells to have the formulas in them, so that when I begint o populate, the calculations are automatic.

See More: How do I add rows to a spreadsheet and keep t

Report •


#1
November 18, 2010 at 20:07:09
Right Click the Row Number(s) of the Row(s) with the formulas you want and choose Copy.

Right Click the Row Number where you want the Row(s) inserted and choose Insert Copied Cells

The Row(s) will be Inserted/Pasted above the row you choose.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#2
November 28, 2010 at 09:56:31
Hi there!
You can use this macro that was written By David McRitchie

Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0)
' Documented:  <a href="http://www.mvps.org/dmcritchie/excel/insrtrow.htm" target="_blank">http://www.mvps.org/dmcritchie/exce...</a>
' 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 Integer
   ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
       Windows(1).SelectedSheets.Count)
   i = 0
   For Each sht In _
       Application.ActiveWorkbook.Windows(1).SelectedSheets
    Sheets(sht.Name).Select
    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. _
     SpecialCells(xlConstants).ClearContents
   Next sht
   Worksheets(shts).Select
End Sub

This macro will insert a row and keep the formula used in the previous row.

Cheers!


Report •

Related Solutions


Ask Question