CODEWITHSUNDEEP
vbaexcelfilter
Hayate Yamato
Hayate Yamato

Reputation: 43

How to hide columns based on name in Excel 2010?

I am trying to hide columns based on name using VBA inside Excel 2010. Each of my columns have a product version and some results below it. The product version does repeat throughout the spreadsheet since I have it categorized by OS. Thus, I'm hiding multiple columns based on selection, like a filter would do. If I could hide based on the name and not the column letter (A,B,C,...), then adding columns in between in the future would prevent more code changes on the location of those columns.

What I'm currently doing right now is fixed to the column letter. This limits me in the sense that I cannot add columns in between without having to change the code (column letter). Example:

`If productver_2dot5.Value = True Then
   Columns("E").Hidden = False
   Columns("M").Hidden = False
   Columns("AC").Hidden = False
   Columns("AT").Hidden = False
   Columns("BD").Hidden = False
   Columns("BR").Hidden = False
Else
   Columns("E").Hidden = True
   Columns("M").Hidden = True
   Columns("AC").Hidden = True
   Columns("AT").Hidden = True
   Columns("BD").Hidden = True
   Columns("BR").Hidden = True
End If`

What I would like to do is to hide any columns that contains the name 'Product Ver 2" (example) in one of its cells.

Upvotes: 0

Views: 3481

Answers (1)

Tim Williams
Tim Williams

Reputation: 166351

Sub HideBlahs()
    Dim col As Range
    For Each col In ActiveSheet.UsedRange.Columns
         If Application.CountIf(col, "blah") > 0 Then
             col.EntireColumn.Hidden = True
         End If
    Next col
End Sub

FYI your posted code reduces to:

Range("E1,M1,AC1,AT1,BD1,BR1").EntireColumn.Hidden = Not productver_2dot5.Value

Upvotes: 3

Related Questions