Solved: VBA Hide Rows if Cell Value is #N/A

[jammer6_9]

[vba]
Sub hide_if_error()
Dim MyCell As Range, Rng As Range
Set Rng = Range("c8:c27")
For Each MyCell In Rng
If MyCell = IsError??? Then
MyCell.EntireRow.Hidden = True
End If
Next MyCell
End Sub
[/vba]

[Bob Phillips]

[vba]

Sub hide_if_error()
Dim MyCell As Range, Rng As Range
Set Rng = Range("c8:c27")
For Each MyCell In Rng
If IsError(MyCell) Then
MyCell.EntireRow.Hidden = True
End If
Next MyCell
End Sub
[/vba]

But hiding errors seems like a bad idea to me.

[jammer6_9]

But hiding errors seems like a bad idea to me.

Same here but as it was requested by the user so I must give it ... How can I apply this to work in multiple worksheets (Sheet20 upto sheet80) in a command button

[Bob Phillips]

Same range for each sheet, or different? Set sheets, or variable?

[jammer6_9]

same range on each sheet...

[Bob Phillips]

[vba]

Sub hide_if_error()
Dim MyCell As Range, Rng As Range

For Each sh In ActiveWorkbook.Worksheets
Set Rng = sh.Range("c8:c27")
For Each MyCell In Rng
If IsError(MyCell) Then
MyCell.EntireRow.Hidden = True
End If
Next MyCell
Next sh
End Sub
[/vba]

[Krishna Kumar]

Hi,

I think a loop can be avoided. See

[vba]On Error Resume Next
Range("c8:c27").SpecialCells(xlCellTypeFormulas, 16).EntireRow.Hidden = True
Range("c8:c27").SpecialCells(xlCellTypeConstants, 16).EntireRow.Hidden = True
On Error GoTo 0[/vba]
HTH

[jammer6_9]

Solved

[RonMcK]

Krishna,

Why not use 'xlErrors' instead of the number 16 (its numeric equivalent) since the term is more discriptive?
[vba]On Error Resume Next
Range("c8:c27").SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Hidden = True
Range("c8:c27").SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Hidden = True
On Error GoTo 0[/vba]
Thanks,

[Krishna Kumar]

Krishna,

Why not use 'xlErrors' instead of the number 16 (its numeric equivalent) since the term is more discriptive?
[vba]On Error Resume Next
Range("c8:c27").SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Hidden = True
Range("c8:c27").SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Hidden = True
On Error GoTo 0[/vba]
Thanks,

Well, that was recorded macro. Will keep in mind.