Visual Basic Debug in Excel

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.

KristenKAB

Posted 2015-03-17T20:34:03.943

Reputation: 1

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. – I say Reinstate Monica – 2015-03-17T20:38:28.843

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! – Mathieu Guindon – 2015-03-17T20:51:07.230

2It'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 – 2015-03-17T20:53:58.483

1When 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 – 2015-03-17T20:57:29.120

Also NwsFirstRow and NwsFirstCol sound like they're a range. So it may just be a matter of redifining these ranges. – LPChip – 2015-03-17T20:58:05.283

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. – I say Reinstate Monica – 2015-03-17T21:49:24.917

Restore from backup – DavidPostill – 2015-03-17T22:30:36.170

Visual Basic Debug in Excel

Answers