VBA Hide Column based on cell values

[JoeRooney]

Hi ,

I need some code to hide a column if all the cells have been reported as n/a

My data always starts on row 3 , the first two rows are headers and the number of rows would need to be dynamic as it could change.,

Example , from the below table the macro would hide column C

A	B	C
Test 1	Test 2	Test 3
Test	Test	n/a
Test	Test	n/a
Test	n/a	n/a
Test	n/a	n/a
Test	n/a	n/a

I have been looking online and there are a lot of examples of hiding rows based on cell value but I cant find anything to hide columns based on the cell value of all the cells in that column.

Any help is greatly appreciated

Thanks.

 

[alansidman]

Option Explicit

Sub ATest()
    Dim lr As Long, i As Long, lc As Long, x As Integer, j As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
    For i = 2 To lc
        x = 0
        For j = 3 To lr
            If Cells(j, i) = "n/a" Then
                x = x + 1
            End If
        Next j
        If x = lr - 2 Then
            Columns(i).Hidden = True
        End If
    Next i
End Sub

 

[mikerickson]

Are those n/a user enteries or are they formulas returning a #N/A error?

 

[EXCEL MAX]

Hello, this one is similar...

Sub HideErrorColumns()
 
    Dim varWS As Worksheet
    Dim varNLoops As Integer, _
        varNRows As Long, varNColumns As Integer
    Dim varNErrors As Integer
 
    Application.ScreenUpdating = False
    Set varWS = Worksheets("YourSheetName")
    varNRows = varWS.UsedRange.Rows.Count
    varNColumns = varWS.UsedRange.Columns.Count
 
    For varNLoops = 1 To varNColumns
        varNErrors = WorksheetFunction.CountIf(varWS.Range(Cells(3, varNLoops), _
            Cells(varNRows, varNLoops)), "#N/A")
        If varNErrors > 0 Then varWS.Columns(varNLoops).Hidden = True
    Next
    Application.ScreenUpdating = True
 
End Sub

 

[JoeRooney]

Hi [U]alansidman[/U] your code works perfectly .

Thanks everyone for your suggestions.