VBA Macro Column Hiding

[MikeRob]

Need help if anyone can provide it. I'm trying to write a Macro where i can hide columns based on a cell value. The issue I'm having is trying to figure out how to construct it in a manner where I can have the macro work based on multiple cell values as well as work throughout the entire workbook that's has over 400 tabs. what I have only works with one cell value and only on the current worksheet.

here is the VBA code I have:

Sub HideCols()
Dim cell As Range
For Each cell In ActiveWorkbook.ActiveSheet.Rows("8").Cells
If cell.Value = "X" Then
cell.EntireColumn.Hidden = True
End If
Next cell
End Sub

I'm fairly new to Macros so id like to know what I'm doing wrong, how to correct it to achieve hiding columns based on multiple cell values and work for the entire workbook, and an explanation of how you got it to work if possible. Your time and help is greatly appreciated

 

[Zot]

Just want to make sure to thank you for your help. I did some tinkering with it and found something that worked. It was close to what you described the first time. This is what I used:

Sub loopSheets()
For Each ws In ActiveWorkbook.Worksheets
For Each cell In ws.Rows("3").Cells
If cell.Value = "Registered Voters" Or cell.Value = "IVO" Or cell.Value = "Emergency" Or cell.Value = "Absentee" Or cell.Value = "Provisional" Or cell.Value = "Military" Then
cell.EntireColumn.Hidden = True
End If
Next cell
Next ws
End Sub

so you were on point with the 1st code shared with me, but the small difference that made this work was instead of (For Each cell In ActiveWorkbook.ActiveSheet.Rows("3").Cells) I used (For Each cell In ws.Rows("3").Cells).

but just still wanted to thank you for you're time and effort with this, definitley appreciated

Use Select Case like Fluff showed is more shorter and more tidy if you have IF OR condition.

 

[Peter_SSs]

Further, Fluff's code is also much more efficient than the code you are using because it only bothers to check columns in the worksheet that might have a value in row 3. The code you are using ( & the code Zot posted) is checking all 16,384 columns in every sheet even though (I assume) you are using nothing like that many columns.

 

[MikeRob]

Further, Fluff's code is also much more efficient than the code you are using because it only bothers to check columns in the worksheet that might have a value in row 3. The code you are using ( & the code Zot posted) is checking all 16,384 columns in every sheet even though (I assume) you are using nothing like that many columns.

Thank you for that important information.... So that's why the macro ran slow. Yes I certainly didn't need it to check all 16K cells in the row

 

[Zot]

Thank you for that important information.... So that's why the macro ran slow. Yes I certainly didn't need it to check all 16K cells in the row

No argument on what @Peter_SSs said. I was thinking about mentioning the last column with data but forget about it. ? Note that @Fluff is using UsedRange which limit number of column.

I think the SpecialCell xlCellTypeConstants will do too. Internal build-in function, though checking each column will run much faster than code user wrote. Well, I'm not a programmer and don't even write a single VBA line perhaps for months until I joint this Forum. Just learning from Internet from expert like @Fluff and @Peter_SSs

 

[MikeRob]

No argument on what @Peter_SSs said. I was thinking about mentioning the last column with data but forget about it. ? Note that @Fluff is using UsedRange which limit number of column.

I think the SpecialCell xlCellTypeConstants will do too. Internal build-in function, though checking each column will run much faster than code user wrote. Well, I'm not a programmer and don't even write a single VBA line perhaps for months until I joint this Forum. Just learning from Internet from expert like @Fluff and @Peter_SSs

I know that's right and I'm learning from all you you... From anyone who participates in these forum threads. Jot down everyone's input when posting. That way I may be able later to help some in the same situation as well as there's more than one way to do things sometimes.