VBA - Insert row, copy contents of original row except for contents of column A

**Royzer** · 02-20-2012, 07:06 PM

Hi. I am using the code below to insert a row by double-clicking a cell. The code then copies formulas (and apparently dates) from the original row to the new row. Is there any way for me to adjust this code so the cell in column A is blank after the insert? If so, I need it to work like this for all 30+ pages of the workbook. Here's the code I have in ThisWorkbook:

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
  'David McRitchie,  2007-09-07    insrtrow.htm on double-click
  '-- will copy more often than  Extend Formulas and Format (tools option)
  Cancel = True
  Target.EntireRow.Copy
  Cells(Target.Row + 1, 1).EntireRow.Insert
  Cells(Target.Row + 1, 1).EntireRow.Select
  ActiveSheet.Paste
  Application.CutCopyMode = False
  On Error Resume Next
  '-- customize range for what cells constants can be removed --
  Intersect(Selection, Range("b:IV")).SpecialCells(xlConstants).ClearContents
  On Error GoTo 0
End Sub

Thanks!

**Royzer** · 02-20-2012, 07:36 PM

It's been solved. Here's the new code:

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    With Target
        .Offset(1).EntireRow.Insert
        .EntireRow.Copy .Offset(1).EntireRow(1)
        With .Offset(1).EntireRow
            .Cells(1).ClearContents
            On Error Resume Next
            .SpecialCells(2).ClearContents
            On Error GoTo 0
        End With
    End With
End Sub

Thanks!

**smuzoen** · 02-20-2012, 07:41 PM

If you want to make the cell in column A blank you would just add in one line to the last section

'-- customize range for what cells constants can be removed --
  Intersect(Selection, Range("b:IV")).SpecialCells(xlConstants).ClearContents
  Cells(Target.Row + 1, 1).ClearContents ' Will clear cell in Column A
  On Error GoTo 0

By placing this macro in ThisWorkbook as you have done it will work on every sheet in the workbook. You do realise that the line

 Intersect(Selection, Range("b:IV")).SpecialCells(xlConstants).ClearContents

Will remove any values that are in the sheet that are not the result of formulas e.g if you have placed the number 5 in a cell and run the macro on the row with this number then the number 5 will be cleared. This also goes for any text that is not the result of formulas - if you don't want that to happen then remove that line of code.