Clear certain columns after row insert-CodePudding

I have the following code, which adds a row after double clicking on the row above where you want the new row inserted:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    Target.Offset(1).EntireRow.Insert
    Target.EntireRow.Copy Target.Offset(1).EntireRow
    On Error Resume Next
    Target.Offset(1).EntireRow.SpecialCells (xlConstants).ClearContents
                        
End Sub

When you run that, it clears the entire row of all content with the .ClearContents.

What I would like to see happen is, after the end user double clicks on a row, a new row is inserted, but it ONLY clears content in columns E through R, as well as T.

So, I tried this code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    Target.Offset(1).EntireRow.Insert
    Target.EntireRow.Copy Target.Offset(1).EntireRow
    On Error Resume Next
    Target.Offset(1).EntireRow.SpecialCells (xlConstants)
    
    'need to clear data from columns E through R and column T
    Target.Range("E:R").ClearContents
    Target.Range("T").ClearContents
                        
End Sub

I thought the Target would pick up the row, but unless I define a specific cell number, it doesn't work. In other words, if I changed the code to this, it would work, but only (and always) clear row 10:

Target.Range("E10:R10").ClearContents

But the row number could vary depending on where they double click... but I'm not sure how to make that dynamic.

Any help would be greatly appreciated!

CodePudding user response：

Please, try this code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    Target.Offset(1).EntireRow.Insert
    Target.EntireRow.Copy Target.Offset(1).EntireRow
     Intersect(Target.Offset(1).EntireRow, Range("E:R,T:T")).ClearContents
End Sub

CodePudding user response：

Using Cells notation might be easier. This approach also uses the Union method.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Cancel = True
Target.Offset(1).EntireRow.Insert
Target.EntireRow.Copy Target.Offset(1).EntireRow
On Error Resume Next
Target.Offset(1).EntireRow.SpecialCells (xlConstants)
On Error GoTo 0

'need to clear data from columns E through R and column T
Union(Range(Cells(Target.Row, "E"), Cells(Target.Row, "R")), Cells(Target.Row, "T")).offset(1).ClearContents
                        
End Sub