Macro to hide rows and columns based on cell values

[ttbuson]

I have data in an array like A1:Z38. I have formulas in each of the cells that either display a number or #N/A. I would like to write a macro that will go through each of the rows in the array and then hide the rows where all the cells contain #N/A. Then, I would like it to cycle through the array by columns and hide all the columns where all the cells contain #N/A.

Tried searching the forum, but couldn't exactly what I was looking for. Thanks in advance!

 

[JoeMo]

Try this on a copy of your worksheet:

Sub HideErrantRwsCols()
Dim rng As Range, vA As Variant, dRws As Range, dCols As Range
Set rng = ActiveSheet.Range("A1:Z38")
vA = rng.Value
'check rows
For i = LBound(vA, 1) To UBound(vA, 1)
    For j = LBound(vA, 2) To UBound(vA, 2)
        n = n + 1
        If Not IsError(vA(i, j)) Then Exit For
     Next j
     If n = rng.Columns.Count Then
        If dRws Is Nothing Then
            Set dRws = rng.Rows(i)
        Else
            Set dRws = Union(dRws, rng.Rows(i))
        End If
    End If
    n = 0
Next i
'check columns
For j = LBound(vA, 2) To UBound(vA, 2)
    For i = LBound(vA, 1) To UBound(vA, 1)
        n = n + 1
        If Not IsError(vA(i, j)) Then Exit For
    Next i
    If n = rng.Rows.Count Then
        If dCols Is Nothing Then
            Set dCols = rng.Columns(j)
        Else
            Set dCols = Union(dCols, rng.Columns(j))
        End If
    End If
    n = 0
Next j
'hide rows
If dRws Is Nothing Then
    MsgBox "No rows with all errors in Range " & rng.Address
Else
    dRws.EntireRow.Hidden = True
End If
'hide columns
If dCols Is Nothing Then
    MsgBox "No columns with all errors in Range " & rng.Address
Else
    dCols.EntireColumn.Hidden = True
End If

End Sub