Error 13, type mismatch after row deleted in Worksheet_Change

Question

I have an Excel spreadsheet with two sheets. In sheet1 I have multiple rows each with a dropdown which is used to set the status of the row. If the status gets changed to 'Completed' or 'On Hold' it should get deleted from sheet1 and moved to the next available row in sheet2.

However after it is deleted from sheet1 I get

Run-time error 13 - type mismatch

Below is a screenshot of the highlighted code, link to screen capture of the error, screenshot of sheet1 and the highlighted debug code.

https://youtu.be/7xbinC6meHw

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

    Set KeyCells = Range("B:B")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

            If (Target.Value = "Complete" Or Target.Value = "On Hold") Then
                ActiveCell.EntireRow.Copy
                Worksheets("Sheet2").Activate
                i = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
                Worksheets("Sheet2").Cells(i + 1, 1).Select
                ActiveSheet.Paste
                Worksheets("Sheet1").Activate
                ActiveCell.EntireRow.Delete
            End If

    End If
End Sub

This might help you, you shouldn't use Select and Activate: How to avoid using Select in Excel VBA. — erazorv4
besides what erazorv4 said, the If statement doesn't work between () you need to take them off — Damian
@Damian that's not true. Nothing wrong with that If statement — Tim Stack
OP, could it be that the target range consists of more than one cell? — Tim Stack
Too late to edit my comment but: Debug through the code and look closely at what Target is and the Type of Target.Value is. Check the Address from target, it might be because something is changing on the worksheet (like when you're doing ActiveCel.EntireRow.Delete) And that range IS intersecting with "B:B" because its the entire row. So I think that is the problem. Type Mismatch is caused by comparing wrong types with each other. So Target might be a differend cell then you expect. — erazorv4

FunThomas FunThomas · Accepted Answer · 2019-06-20T08:13:07

This is a common problem for Worksheet_Change-routines that modify the sheet itself - this will trigger a new Change-Event (In that second event, target is the complete row that is currently deleted and checking the value of a Range with more than one cell will raise this error 13).

It is easy to prevent such problems: you have to disable events while the event-routine is running.

Update: Modified the code to show how to use Copy without Select

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Application.EnableEvents = False  ' Disable events while routine is doing its duty
    On Error Goto ChangeExit          ' Ensure that events are switched on in any case

    Set KeyCells = Range("B:B")
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then

        With Target.Cells(1, 1)
            If (.Value = "Complete" Or .Value = "On Hold") Then
                Dim lastRow As Long
                lastRow = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).row

                .EntireRow.Copy Worksheets("Sheet2").Cells(lastRow + 1, 1)
                .EntireRow.Delete
            End If
        End With
    End If
ChangeExit:
    Application.EnableEvents = True
End Sub

Error 13, type mismatch after row deleted in Worksheet_Change

1 Answers