Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert row with formulae without contents
Catastrophe! I did not keep a copy of a macro which inserted a row above and
copied everything from the row below into the new row except any contents and fill color. The formula in some of the cells was copied but none of the contents so the new row was ready for entries One of you helped with this several years ago and it has been very useful €“ please help again. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert row with formulae without contents
Sub InsertNewRow()
ActiveCell.EntireRow.Insert Rows(ActiveCell.Row + 1).Copy Cells(ActiveCell.Row, 1).PasteSpecial Paste:=xlPasteFormulas Application.CutCopyMode = False End Sub -- __________________________________ HTH Bob "camlad" wrote in message ... Catastrophe! I did not keep a copy of a macro which inserted a row above and copied everything from the row below into the new row except any contents and fill color. The formula in some of the cells was copied but none of the contents so the new row was ready for entries One of you helped with this several years ago and it has been very useful - please help again. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert row with formulae without contents
Thanks Bob - this was the trouble I had last time - the macro copies
everything - I want a clear row with only the formula which is in some of the cells - all others empty and without any contents or fill (apart of course from the formula) which the cell below might have. ? camlad "Bob Phillips" wrote: Sub InsertNewRow() ActiveCell.EntireRow.Insert Rows(ActiveCell.Row + 1).Copy Cells(ActiveCell.Row, 1).PasteSpecial Paste:=xlPasteFormulas Application.CutCopyMode = False End Sub -- __________________________________ HTH Bob "camlad" wrote in message ... Catastrophe! I did not keep a copy of a macro which inserted a row above and copied everything from the row below into the new row except any contents and fill color. The formula in some of the cells was copied but none of the contents so the new row was ready for entries One of you helped with this several years ago and it has been very useful - please help again. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert row with formulae without contents
Is there a macro that will copy the row above into only highlight cells.
Would I change the +1 to -1? Can you please help. I have a subtotal function that I would like to fill the blanks with the row above for only visible blank cells. Help please.... "Bob Phillips" wrote: Sub InsertNewRow() ActiveCell.EntireRow.Insert Rows(ActiveCell.Row + 1).Copy Cells(ActiveCell.Row, 1).PasteSpecial Paste:=xlPasteFormulas Application.CutCopyMode = False End Sub -- __________________________________ HTH Bob "camlad" wrote in message ... Catastrophe! I did not keep a copy of a macro which inserted a row above and copied everything from the row below into the new row except any contents and fill color. The formula in some of the cells was copied but none of the contents so the new row was ready for entries One of you helped with this several years ago and it has been very useful - please help again. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert row with formulae without contents
Bob, I have found the macro I had lost but, having pasted it into the
PERSONAL.XLS module it will not run - have I missed something when I copied it into the Word doc where I saved it? I do hope you can help - it is imortant for me to get it going soon and it is way beyond me - as you can see it is the work of others. €˜Insert row reproducing formulae from row below Sub InsertRowsAndFillFormulas(Optional vRows As Long) Application.ScreenUpdating = False ' Documented: http://www.mvps.org/dmcritchie/excel/insrtrow.htm ' Insert Rows -- 1997/09/24 Mark Hill 'Dim vRows As Integer (This put at the beginning of this Module ' row selection based on active cell -- rev. 2000-09-02 David McRitchie ActiveCell.EntireRow.Select 'So you do not have to preselect entire row If vRows < 1 Then 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 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).SelectedShee ts Sheets(sht.Name).Select i = i + 1 shts(i) = sht.Name 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 Application.Goto reference:="R[1]C1" End Sub Thanks camlad "Bob Phillips" wrote: Sub InsertNewRow() ActiveCell.EntireRow.Insert Rows(ActiveCell.Row + 1).Copy Cells(ActiveCell.Row, 1).PasteSpecial Paste:=xlPasteFormulas Application.CutCopyMode = False End Sub -- __________________________________ HTH Bob "camlad" wrote in message ... Catastrophe! I did not keep a copy of a macro which inserted a row above and copied everything from the row below into the new row except any contents and fill color. The formula in some of the cells was copied but none of the contents so the new row was ready for entries One of you helped with this several years ago and it has been very useful - please help again. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA - Insert row, copy contents of original row except for contents of columns A-N | Excel Programming | |||
VBA - Insert row, copy contents of original row except for contents of column A | Excel Programming | |||
Delete the contents of all cells which dont contain formulae | Excel Discussion (Misc queries) | |||
Macro to Insert a row and copy Formulae | Excel Programming | |||
Delete cell contents that contain numbers and not formulae ... | Excel Programming |