Thread: Event macro: insert row with formulae based on current row, clear selected contents
View Single Post
 
Old 04-16-2020, 05:37 PM
emsa emsa is offline Windows 7 64bit Office 2010 64bit
Novice
  
Join Date: Sep 2013
Posts: 4
emsa is on a distinguished road
Default
Thank you NoSparks for the prompt reply.

I added the line to the macro and it adds the row but does not remove any values at all on the actual spreadsheet in question. I know your line of code is good because I tested it on another spreadsheet albeit with different formulas.


I've attached the spreadsheet Planning INSERT ROW BELOW TEST.xlsm if you wouldn't mind taking a look. Many thanks again.

Here's the macro...

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Cancel = True   'Eliminate Edit status due to doubleclick
    Target.Offset(1).EntireRow.Insert
    Target.EntireRow.Copy Target.Offset(1).EntireRow
    On Error Resume Next
     Intersect(Target.EntireRow.Offset(1), UsedRange).Offset(, 2).SpecialCells(xlConstants).ClearContents
    'Target.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents
    On Error GoTo 0
End Sub

Regards,

emsa
Reply With Quote