# Macro to hide rows and columns based on cell values

#### ttbuson

##### Board Regular
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!

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### JoeMo

##### MrExcel MVP
Try this on a copy of your worksheet:
Code:
``````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``````

Replies
10
Views
118
Replies
3
Views
27
Replies
5
Views
58
Replies
3
Views
35
Replies
14
Views
162