Delete entire based on another cell value-CodePudding

I need help with Excel VBA code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 And Target.Cells.Count = 1 Then
    If LCase(Target.Value) = "-1" Then
        With Target.EntireRow.ClearContents
        End With
    End If
End If
If Target.Column = 3 And Target.Cells.Count = 1 Then
    If LCase(Target.Value) = "1000" Then
        With Target.EntireRow
            .Copy Sheets("Week Schedule").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
            .Delete
        End With
    End If
End If
End Sub

If the third column we enter -1 it will clear the row. If we enter 1000 it will be copied to another sheet and deleted from the current sheet.

The above code is working fine. Instead of clearing row data, I want to delete that row. So added

Line 4 With Target.EntireRow.ClearContents to With Target.EntireRow.Delete

But it shows an error.

CodePudding user response：

It would help to know what error you get. Assuming the error is caused because the Week Schedule sheet does not exist, you can add a check for that. After that, your code works fine:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 And Target.Cells.Count = 1 Then
    If LCase(Target.Value) = "-1" Then
        With Target.EntireRow.ClearContents
        End With
    End If
End If
If Target.Column = 3 And Target.Cells.Count = 1 Then
    If LCase(Target.Value) = "1000" Then
        With Target.EntireRow
            SheetExistsOrCreate ("Week Schedule")
            .Copy Sheets("Week Schedule").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
            .Delete
        End With
    End If
End If
End Sub

Function SheetExistsOrCreate(name As Variant)
For i = 1 To Worksheets.Count
    If Worksheets(i).name = "MySheet" Then
        exists = True
    End If
Next i

If Not exists Then
    Worksheets.Add.name = name
End If

End Function

CodePudding user response：

Please, try the next adapted code:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 3 And Target.Cells.Count = 1 Then
    Application.EnableEvents = False
     If LCase(Target.Value) = -1 Then        
        Target.EntireRow.Delete
     ElseIf Target.Value = 1000 Then
        With Target.EntireRow
            .Copy Sheets("Week Schedule").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
            .Delete
        End With
    End If
    Application.EnableEvents = True
 End If
End Sub

The above code assumes that the Target value means a number, not a string looking as a number. If a string, you can place them between double quotes, as in your initial code.

Of course, a sheet named "Week Schedule" must exist in the active workbook and must not be protected.