0

I had a workbook created for me.

I made a mistake and deleted an entire row from the Master Page and now I am getting a debug error and #REF! on the UC-K sheet.

The error says Run-Time Error '13': Type mismatch.

When I Debug the following line is highlighted in Yellow:

If .Cells(R, NwsLastCol).Value = 0 Then

The whole code in that section is:

Public Sub HideRows(ByRef Ws As Worksheet)

    Dim R As Long

    ScreenAndEvents False
    With Ws
        .Rows.Hidden = False
        For R = NwsFirstRow To LastRow(NwsFirstCol, Ws)
            If .Cells(R, NwsLastCol).Value = 0 Then .Rows(R).Hidden = True
        Next R
    End With
    ScreenAndEvents True
End Sub

Please help!! :) I am willing to share the workbook if needed, but I'd like to know how to fix it myself as well.

7
  • Try adding the line On Error Resume Next after the line Dim R As Long. That will cause VBA to keep processing the code after encountering the cell containing the #REF error. Mar 17, 2015 at 20:38
  • 4
    @Twisty that's very bad advice. On Error Resume Next just shoves whatever error you get, under the carpet, letting the code happily continue with bad/unknown values. Handle errors, don't hide them! Mar 17, 2015 at 20:51
  • 2
    It's not going to be possible to assist you in debugging this without seeing a lot more of the workbook and the VBA code. NwsFirstRow is not a built-in VBA object; it has to be a global object defined elsewhere in the custom VBA code. By deleting that row, you've broken NwsFirstRow somehow. A Type Mismatch error in this context is pretty much impossible to debug without getting one's hands on the entire thing, since it could refer to .Cells or .Value.
    – hBy2Py
    Mar 17, 2015 at 20:53
  • 1
    When you press debug, it should hilight the code that throws the error. If you then hover your mouse on the R, you can see the value it last had before the debugger stopped the code. You'll need to look into what this is to find the row that throws in the error. Also, try recreating the rows you accidentally deleted, as its likely going to be necessary to solve your code.
    – LPChip
    Mar 17, 2015 at 20:57
  • 1
    @Mat'sMug I completely agree errors should be handled appropriately; but for a user that obviously cannot maintain the code himself, there's a tradeoff between skipping a bad value and writing error-handling code that would...skip the bad value. To your point perhaps the Resume Next should be placed closer to the .Hidden method, followed by On Error Goto 0, but using Resume Next is a perfectly acceptable way of dealing with invalid values on the user's worksheet. Mar 17, 2015 at 21:49

1 Answer 1

0

To avoid Type Mismatches

replace:

If .Cells(R, NwsLastCol).Value = 0

with:

If .Cells(R, NwsLastCol).Text = "0"

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .