MrExcel Publishing
Your One Stop for Excel Tips & Solutions


Posted by Mike Schofield on February 12, 2002 8:43 AM

Is there any way such that when I insert a row, between existing rows that contain formula, the formula is automatically placed in the correct column cell of the inserted row.

Posted by Joe Was on February 12, 2002 9:31 AM

Only by a macro like this!

Copy the code below, to the sheet module where it will be used (click the sheet tab select "View Code."), then select Macro-Options and assign a hot-key. (I used "i" for insert.) The code will insert a blank new line at the cursor location and copy the Format, Formulas and Comments from the ROW above the current selection to the new ROW. Note: the code can be changed to select the model ROW down from the selection if desired. JSW

Sub AInsert()
'AInsert Macro
'By Joe Was, 2/2002

'Insert new row at cursor.
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown

'Copy all except the Value & Validation of the next ROW down.
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
'Note: Change "rowOffset:=" to "-1" to copy the next ROW up!

'Paste all except the Value & Validation to the new ROW.
ActiveCell.Offset(rowOffset:=-1, columnOffset:=0).Activate
'Note: If "rowOffset:=" above was changed, do the opposite here!
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteComments, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub

Posted by Mamillius on February 12, 2002 2:20 PM

Or .....

Sub InsertRow()
With ActiveCell.EntireRow
.Copy .Offset(-1, 0)
.Offset(-1, 0).SpecialCells(xlCellTypeConstants, 23).ClearContents
End With
End Sub