samedi 24 octobre 2020

Excel VBA BeforeDoubleClick delete row after copying cell values

Is it possible to add a command like TargetRow.Delete Shift:=xlUp inside the if function in BeforeDoubleClick to delete the the array row after being copied to Worksheets("Eingabe")?

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

Dim Trigger     As range
Dim Arr         As Variant            
Dim Src         As Variant             
Dim Tgt()       As String               
Dim R           As Long

Set Trigger = range(Cells(2, "F"), Cells(Rows.Count, "F").End(xlUp))
If Not Application.Intersect(Target, Trigger) Is Nothing Then
    R = Target.Row
    Arr = range(Cells(R, 1), Cells(R, Columns.Count).End(xlToLeft)).Value
    
    Src = Array(1, 2, 3, 4, 5)
    Tgt = Split("B2,B5,B6,B3,B4", ",")
    
    For R = 0 To UBound(Src)
        Worksheets("Eingabe").range(Tgt(R)).Value = Arr(1, Src(R))
    Next R
    Cancel = True              
End If

End Sub

Aucun commentaire:

Enregistrer un commentaire