Macro to hide rows and columns based on cell values

ttbuson

Board Regular
Joined
Nov 18, 2011
Messages
80
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!
 

Some videos you may like

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
Joined
May 26, 2009
Messages
17,194
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,677
Messages
5,524,230
Members
409,566
Latest member
santoshsj

This Week's Hot Topics

Top