Run Time Error 13 Type Mismatch

[Razzer]

Hi, very new to vba and this forum, which has been very helpful.

I have some code that works perfectly but always gives a 'Run Time Error 13' and stops the macro from completing.

Any help please?

'Delete a row of data
Dim rngSrc As Range
Dim NumRows As Integer
Dim ThisRow As Integer
Dim ThatRow As Integer
Dim J As Integer

'Select rows to be deleted
Set rngSrc = Sheet2.Range("J1:J250")
NumRows = rngSrc.Rows.Count
ThisRow = rngSrc.Row
ThatRow = ThisRow + NumRows - 1
'Delete rows
For J = ThatRow To ThisRow Step -1
If Cells(J, "J") = "8" Then
Rows(J).Select
Selection.Delete Shift:=xlUp
DeletedRows = DeletedRows + 1
End If
Next J

Line in bold is always highlighted when debugged.

many thanks

 

[Joe4]

Welcome to the Board!

Maybe try changing J from "Integer" to "Long"?
Integer caps up at 32767, and the number of rows in Excel can exceed that.

Also, are your entries in column J Text or Numeric? If Numeric, change it to:
If Cells(J, "J") = 8 Then
(no quotes around the 8).

 

[Peter_SSs]

Welcome to the MrExcel board!

You would expect that error if the code encounters an error value in a cell in your column J range. When the code errors, click Debug then hover over the variable J to see what the problem row is then look in that row in column J.

 

[Razzer]

Hi Joe & Peter,

J column does have numeric values and no joy with changes. This code has been working perfectly for last two weeks but I have recently made available to other PC's, since this time the error has occurred. Could this be part of the problem, they are using excel2010 whereas I am using excel2016

 

[Joe4]

Try running this version with error handling. It will tell you which row is causing the error. Then take a look at the cell and see if there is anything odd about it.
If you cannot tell, tell us the row number and the value in column J of that cell (it is not protected or merged cell, is it?)

Sub MyMacro()
'Delete a row of data

    Dim rngSrc As Range
    Dim NumRows As Integer
    Dim ThisRow As Integer
    Dim ThatRow As Integer
    Dim J As Integer

'Select rows to be deleted
    Set rngSrc = Sheet2.Range("J1:J250")
    NumRows = rngSrc.Rows.Count
    ThisRow = rngSrc.Row
    ThatRow = ThisRow + NumRows - 1
    
'Delete rows
    On Error GoTo err_chk
    For J = ThatRow To ThisRow Step -1
        If Cells(J, "J") = "8" Then
            Rows(J).Select
            Selection.Delete Shift:=xlUp
            DeletedRows = DeletedRows + 1
        End If
    Next J

    Exit Sub
    
err_chk:
    MsgBox "Error number: " & Err.Number & vbCrLf & _
            "Error is on line: " & J
    
End Sub

 

[Jonmo1]

Following up on what Peter said.

To handle the occurance of an error value within column J values, try

If Cells(J, "J").Text = "8" Then

 

[Razzer]

Hi Joe,

Thank you problem fixed. I had copied the formula in column J and thrown an error in the headings row...N/A.

Thanks again appreciate your help

 

[Peter_SSs]

.. thrown an error .....N/A.

Hehe - as suggested in post #3

 

[Joe4]

Hi Joe,

Thank you problem fixed. I had copied the formula in column J and thrown an error in the headings row...N/A.

Thanks again appreciate your help

You are welcome!
And now you learned a new debugging technique too!