CODEWITHSUNDEEP
excelvba
Breakingfaith
Breakingfaith

Reputation: 63

Clearing Contents of a Variable Row

So I am new to this language and am trying to get my head around it. This piece of code is for work and the section here was hugely assisted by an amazing person on this website.

This piece should copy over a row of information to a different spreadsheet when the box in the I Column turns to 7. And thanks to that person it works pretty perfectly.

The next question on my mind though is how to the delete the original row in the first spreadsheet.

I can't use a range like "A9:M9" as the row will not always been in that fixed place.

I tried using: If Source.Column = 9 And Source.Value = "7 - engaged" Then
Range("A:M").Select Selection.ClearContents

But this wiped the entire worksheet. Is there any way to just delete the one row that's been copied?

If Source.Column <> 9 Then Exit Sub If Source.Value <> "7 - engaged" Then Exit Sub

 If MsgBox("Client status selected as engaged. Confirm to post to tank.", 
 vbOKCancel) = vbOK Then
    With ThisWorkbook.Worksheets("Tank")
        Dim rowToPasteTo As Long
        rowToPasteTo = .Cells(.Rows.Count, "B").End(xlUp).Row + 1

        .Range("A" & rowToPasteTo & ":" & "D" & rowToPasteTo).Value = 
Sh.Range("A" & Source.Row & ":" & "M" & Source.Row).Value
        .Range("G" & rowToPasteTo & ":" & "H" & rowToPasteTo).Value = 
Sh.Range("E" & Source.Row & ":" & "F" & Source.Row).Value
        .Range("S" & rowToPasteTo & ":" & "U" & rowToPasteTo).Value = 
Sh.Range("K" & Source.Row & ":" & "M" & Source.Row).Value

    End With

 End If

 If Source.Column = 9 And Source.Value = "7 - engaged" Then`

Upvotes: 0

Views: 94

Answers (1)

Shai Rado
Shai Rado

Reputation: 33662

Not sure if you want to delete the entire row or clear the cells contents:

If Source.Column = 9 And Source.Value = "7 - engaged" Then
    ' Option 1 - deletes the entire row
    Source.EntireRow.Delete       

    ' Option 2 - clears the contents  
    Source.EntireRow.ClearContents
End Sub

Upvotes: 2

Related Questions