VBA Hide entire column if all of cells in the column contain the same text string

Question

Hello!

I would like a VBA code that scans at the cells starting at row 5, working to the bottom of the sheet (which could be of variable length), and hides the columns that contain only cells with "none" in them.

While the number of rows in my sheet could be any amount, my column range will always be A through BV to begin with.
One hang up - I never want the VBA to hide columns AG through AJ.

See below jpegs of an example of my sheet which in this case has only 2 rows. The top is what the sheet looks like pre-VBA and the bottom is what I would like it to look like after VBA (with columns I through O hidden).

Thank you!

Answer 1

Hi.
Please see the code below:

Sub test()

Dim nCol As Integer
Dim nRow As Integer
Dim myRange As Range
Dim cell As Range
Dim hide As Boolean

nRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For nCol = 1 To 74
If nCol < 33 Or nCol > 36 Then
hide = True
Set myRange = Range(Cells(5, nCol), Cells(nRow, nCol))
For Each cell In myRange
If cell <> "none" Then
hide = False
Exit For
End If
Next cell
Else
hide = False
End If
If hide = True Then Columns(nCol).Hidden = True
Next nCol

End Sub