Copy only formulas in multiple inserted rows

Question

I have the following Excel spreadsheet:

      A                B            C
1    Product A         $50         =B1*4
2    Product B         $10         =B2*4
3    Product C         $20         =B2*4

I am using the following formula to insert new rows between row 1 and 2.

Sub Insert_Multiple_Rows()
Tabelle1.Range("2:3").EntireRow.Insert
Tabelle1.Range("2:3").Formula = Tabelle1.Range("1:1").Formula
End Sub

This formula works perfectly and inserts new rows and copies all formulas and values.

However, what I want to achieve is that only the cells with formulas will be copied into the new inserted cells and all other cells remain blank. In the case above that would mean only the formulas in column C are copied into the new inserted rows and columns A and B remain blank in the new inserted rows.

Do you guys have any idea how I can exclude cells that contain a value instead of a formula from the insert row code.

Thanks for your help.

You can use ISFORMULA function from the worksheet to check if the cell contains the formula and instead of copying the entire row, you can copy the cells containing the formula only. Otherwise you can copy entire row and then delete cells not containing formula. — Ethun_Hunt

Rosetta Rosetta · Accepted Answer · 2016-06-15T11:06:06

Here is an option

Sub Insert_Multiple_Rows()
    Tabelle1.Range("2:3").EntireRow.Insert
    Tabelle1.Range("2:3").Formula = Tabelle1.Range("1:1").Formula
    Tabelle1.Range("2:3").SpecialCells(xlCellTypeConstants).ClearContents
End Sub

otherwise here is another solution

Sub Insert_Multiple_Rows()
    Tabelle1.Range("2:3").EntireRow.Insert
    Intersect(Tabelle1.Range("1:1").SpecialCells(xlCellTypeFormulas).EntireColumn, Tabelle1.Range("1:3")).FillDown
End Sub

both give same result

Copy only formulas in multiple inserted rows

2 Answers