Run time error '13': Type Mismatch

thothloki · Dec 9th 2011

Hi all,

I am getting this error when I try to run this code. I am trying to look in column A for "SOFTWARE ZONE" and delete the other rows that do not have that.
I used this same code in another macro with "General Zone" and I have not had a problem with that.

Any ideas?

Thanks in advance

Code

Dim garbage3 As Long
    
    For garbage3 = Sheets("Zones").Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
        If Sheets("Zones").Cells(garbage3, 1) = "SOFTWARE ZONE" Then
        Else
            Sheets("Zones").Rows(garbage3).Delete
        End If
    Next garbage3

rollis13 · Dec 9th 2011

Re: Run time error '13': Type Mismatch

Your script works ok with Excel 2003 so the problem should be searched elsewhere.

widgetwonka · Dec 9th 2011

Re: Run time error '13': Type Mismatch

Not sure what is wrong with that code. It would be faster to use the filter/hidden cells method though. It is well covered on the internet.

You might have better luch declaring garbage3 as an integer.

jindon · Dec 9th 2011

Re: Run time error '13': Type Mismatch

You might have Error in the cell(s)

Code

Dim garbage3 As Long 
With Sheets("Zones")
    For garbage3 = .Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
        If Not IsError(.Cells(garbage3, 1)) Then
            If .Cells(garbage3, 1) <> "SOFTWARE ZONE" Then
                .Rows(garbage3).Delete
            End If
        End If
    Next garbage3
End With

thothloki · Dec 10th 2011

Re: Run time error '13': Type Mismatch

Thank jindon! That worked. It did not delete the error that was found though.
I did find what was causing the issue. When I cut up my text file, there was a cell that contained "-ALARM FUNCT" which then turned itself into "=-ALARM FUNCT" and caused a "#NAME?" error. I was able to fix it by adjusting where I cut my file but I am going to update my VBA code with your code in case something comes up in the future.

I made one alteration to delete any cells with errors. Here is the updated code:

Code

Dim garbage3
    With Sheets("Zones")
    For garbage3 = .Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
        If Not IsError(.Cells(garbage3, 1)) Then
            If .Cells(garbage3, 1) <> "SOFTWARE ZONE" Then
                .Rows(garbage3).Delete
            End If
        End If
        If IsError(.Cells(garbage3, 1)) Then
            .Rows(garbage3).Delete
        End If
    Next garbage3

Display More

Thanks again rollis13, widgetwonka and jindon for all of your help!
I love this website!!!!

jindon · Dec 10th 2011

Re: Run time error '13': Type Mismatch

It would be

Code

Dim garbage3 
With Sheets("Zones") 
    For garbage3 = .Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1 
        If IsError(.Cells(garbage3, 1)) Then
            .Rows(garbage3).Delete
        ElseIf .Cells(garbage3, 1) <> "SOFTWARE ZONE" Then 
            .Rows(garbage3).Delete  
        End If 
    Next garbage3
End With

Richie(UK) · Dec 10th 2011

Re: Run time error '13': Type Mismatch

Quote from widgetwonka;587495

... You might have better luch declaring garbage3 as an integer....

Given the respective ranges of the Integer and Long variable types, when compared to the number of rows in an Excel sheet that could be in use, it is best practice to use Long variables when working with rows. That is, swapping to an Integer is likely to create more problems rather than solve existing ones

Run time error '13': Type Mismatch

Participate now!

Share