Insert row, copy formula, delete values

**inky** · 06-14-2008, 04:23 AM

i'm using the following macro to insert a row, copy formula from above then clear the values. yet when i place it on a row that has no values in cells (say A2 and B2) yet a formula in a cell (say A3) I get a 1004 error "no cells found".

any suggestions on how to prevent this from occurring?

Sub Copy_Formulas_Only()
    Dim row As Single
    row = ActiveCell.row
    Selection.EntireRow.Insert
    Rows(row - 1).Copy
    Rows(row).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas
    Selection.SpecialCells(xlCellTypeConstants).ClearContents
    Application.CutCopyMode = False
End Sub

**mudraker** · 06-14-2008, 04:37 AM

here's 1 way

Sub Copy_Formulas_Only()
    Dim row As Single
    row = ActiveCell.row
    Selection.EntireRow.Insert
    Rows(row - 1).Copy
    Rows(row).Select
   On Error Resume Next
    Selection.PasteSpecial Paste:=xlPasteFormulas
    Selection.SpecialCells(xlCellTypeConstants).ClearContents
   On Error GoTo 0
    Application.CutCopyMode = False
End Sub

**inky** · 06-14-2008, 04:55 AM

thanks mudraker - works great.

so is the "On Error Resume Next" and "On Error GoTo 0" the VBA equivalent of ISERROR/ISNA etc in formula?

**mudraker** · 06-14-2008, 07:19 AM

On Error Resume Next

Specifies that when a run-time error occurs, control goes to the statement immediately following the statement where the error occurred where execution continues.

On Error GoTo 0

Disables any enabled error handler in the current procedure.

e.g disables On Error Resume Next command