PDA

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



jammer6_9
11-05-2008, 02:56 AM
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

xld
11-05-2008, 03:25 AM
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


But hiding errors seems like a bad idea to me.

jammer6_9
11-05-2008, 04:23 AM
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 :rotlaugh: ... How can I apply this to work in multiple worksheets (Sheet20 upto sheet80) in a command button

xld
11-05-2008, 04:39 AM
Same range for each sheet, or different? Set sheets, or variable?

jammer6_9
11-05-2008, 05:03 AM
same range on each sheet...

xld
11-05-2008, 05:21 AM
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

Krishna Kumar
11-05-2008, 06:56 AM
Hi,

I think a loop can be avoided. See

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
HTH

jammer6_9
11-05-2008, 07:06 AM
Solved :friends:

RonMcK
11-05-2008, 11:06 AM
Krishna,

Why not use 'xlErrors' instead of the number 16 (its numeric equivalent) since the term is more discriptive?
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
Thanks,

Krishna Kumar
11-05-2008, 07:41 PM
Krishna,

Why not use 'xlErrors' instead of the number 16 (its numeric equivalent) since the term is more discriptive?
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
Thanks,

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