Event macro: insert row with formulae based on current row, clear selected contents

emsa · #3 04-16-2020, 05:37 PM

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Macro - Copying and Pasting selected slides (based on selected keywords/criteria) Hi	erickhawe	PowerPoint	0	08-16-2019 09:00 PM
Need macro for automatic point value insertion based on the number of answer choices selected	ganesang	Word VBA	29	08-12-2018 09:38 PM
Need macro for automatic point value insertion based on the number of answer choices selected	ganesang	Word	1	07-31-2018 06:07 AM
a macro that can copy data from copy.xls to our current excel macro.xls based on criteria:	udhaya	Excel Programming	1	11-12-2015 10:12 AM
Need macro to fill data from different sheets based on selected item from drop down	skorasika	Excel Programming	1	03-13-2015 11:25 AM