Delete entire based on another cell value

[Sumanthbhe]

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.

Someone, please help.

 

[Alex Blakenburg]

Can you show us your changed code ?
Do you have protection on the sheet ?

The code you have looks a little odd but works in principle, and works fine for me.

It should normally look like either of these 2:-

       Target.EntireRow.Delete  ' Without using the with

       ' Or using the with
        With Target.EntireRow
               .Delete
        End With

 

[Sumanthbhe]

Can you show us your changed code ?
Do you have protection on the sheet ?

The code you have looks a little odd but works in principle, and works fine for me.

It should normally look like either of these 2:-

       Target.EntireRow.Delete  ' Without using the with

       ' Or using the with
        With Target.EntireRow
               .Delete
        End With

Thanks for your reply.

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
                .Delete
            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

Changed the code as per your comment. If I change it to Target.EntireRow.Delete it's not working only EntireRow.ClearContents is working to clear the row.

There is no protection to the working sheet. for other sheets, it's protected to make any changes.
I am not much aware of the VBA code. so I managed to mix two different codes. That's the reason it's looking odd maybe.

It shows the error.
PFA image to check the errors.

 

[Alex Blakenburg]

I can't replicate your issue.
Is the code in the code window of the Sheet, you want it to act on ?
That sheet is NOT protected - right ?
You can try giving us an XL2BB of the sheet it is working on.
I suspect we might need the actual workbook through a shared link though.

I don't think it will make a difference but add the false version to the beginning of the code and the true at the end.

Application.EnableEvents = False
' ..... the rest of the code .....
Application.EnableEvents = True

 

[Alex Blakenburg]

You could also try adding the debug.print lines I have here and see what comes up in the immediate window.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Debug.Print Me.Name
    Debug.Print Target.Address

 

[Alex Blakenburg]

I have now been able to replicate the issue, try this:-

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False

    If Target.Column = 3 And Target.Cells.Count = 1 Then
        If LCase(Target.Value) = "-1" Then
            With Target.EntireRow
                .Delete
            End With
        ElseIf 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

    Application.EnableEvents = True
End Sub

 

[Sumanthbhe]

I have now been able to replicate the issue, try this:-

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False

    If Target.Column = 3 And Target.Cells.Count = 1 Then
        If LCase(Target.Value) = "-1" Then
            With Target.EntireRow
                .Delete
            End With
        ElseIf 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

    Application.EnableEvents = True
End Sub

Excellent. It's Working, Thank you so much.

 

[Alex Blakenburg]

Thanks for letting me know. It was deleting the row when you entered -1, so the “Target” no longer existed when it tried to do the 2nd lot of If Statements.
Glad I could help.