Hide Rows with #N/A error and change #NA values to 0

[tempaccount]

Hello everyone

I'm looking for a macro to hide all rows with a formula error with #N/A but before/after hiding I want to convert these #NA values to zero. Whatever other values in the row should still remain as is though except for the #NA values.

I found a similar thread which I will post below that has a similar case.
http://www.mrexcel.com/forum/showthread.php?t=527381

Thanks

 

[Rick Rothstein]

I'm looking for a macro to hide all rows with a formula error with #N/A but before/after hiding I want to convert these #NA values to zero.

Do you mean you want to replace the formulas with the numerical constant zero?

 

[VoG]

Maybe like this

Sub atest()
With Sheets("Sheet1")
    On Error Resume Next
    With .UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors)
        .Value = 0
        .EntireRow.Hidden = True
    On Error GoTo 0
End With
End Sub

 

[tempaccount]

Yes Rick, I forgot to specify that I wanted it to be numerical not text value. And I tried the above macro but it gave me a debug error saying "Expected End With"

 

[Rick Rothstein]

Maybe like this

Sub atest()
    With Sheets("Sheet1")
        On Error Resume Next
        With .UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors)
            .Value = 0
            .EntireRow.Hidden = True
        End With
        On Error GoTo 0
    End With
End Sub

You accidentally omitted the End With statement I show in red above.

Also, just to point out for the OP in case it matters, the code you posted will replace all cells displaying an error with 0, not just the #N/A ones.

 

[VoG]

Oops!

Sub atest()
With Sheets("Sheet1")
    On Error Resume Next
    With .UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors)
        .Value = 0
        .EntireRow.Hidden = True
    End With
    On Error GoTo 0
End With
End Sub

 

[tempaccount]

Unfortunately it does matter . I need to sum up the column of vlookup formulas and they are associated with names that I need for future years. When the vlookups show the N/A error they will cause a chain reaction and effect other formulas in the sheet like sum & percent formulas.

edit: Silly me, I forgot to mention that I have a macro that paste specials these values as a text value format. So the macro needs to be ran after this process. Hopefully that makes it simpler.

 

[tempaccount]

Does anyone have a solution please

 

[tempaccount]

bump