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]

Then you just loop though each worksheet and loop through each cell like you did above.

Sub loopSheets()
    For Each ws In ActiveWorkbook.Worksheets
        For Each cell In ActiveWorkbook.ActiveSheet.Rows("8").Cells
           If cell.Value = "X" Then
               cell.EntireColumn.Hidden = True
           End If
        Next cell
    Next ws
End Sub

something like that

 

[MikeRob]

Then you just loop though each worksheet and loop through each cell like you did above.

Sub loopSheets()
    For Each ws In ActiveWorkbook.Worksheets
        For Each cell In ActiveWorkbook.ActiveSheet.Rows("8").Cells
           If cell.Value = "X" Then
               cell.EntireColumn.Hidden = True
           End If
        Next cell
    Next ws
End Sub

something like that

 

[MikeRob]

not quite understanding the loop process... seems to still have the same effect, just one column hidden based on one cell, and only one sheet.

I'm sure you probably understand what I'm trying to do, but just in case, the result I'm trying to get is this:

if x is in any column in row 3, hide the entire column
if y is in any column in row 3, hide the entire column
if z is in any column in row 3, hide the entire column... and so on

can you explain how I loop to make this happen? or show the loop code to make this work?

trying to make this all happen simultaneously in this macro and also throughout the whole workbook simultaneously

 

[Zot]

not quite understanding the loop process... seems to still have the same effect, just one column hidden based on one cell, and only one sheet.

I'm sure you probably understand what I'm trying to do, but just in case, the result I'm trying to get is this:

if x is in any column in row 3, hide the entire column
if y is in any column in row 3, hide the entire column
if z is in any column in row 3, hide the entire column... and so on

can you explain how I loop to make this happen? or show the loop code to make this work?

trying to make this all happen simultaneously in this macro and also throughout the whole workbook simultaneously

In your sample macro, i see that you loop through row 8 and when there is letter X in any column in that row, you will hide the entire column.

Now you talked about value in row 3

so it should be

Sub loopSheets()
    For Each ws In ActiveWorkbook.Worksheets
        For Each cell In ActiveWorkbook.ActiveSheet.Rows("3").Cells
            Select Case cell.Value
                Case x, y, y
                    cell.EntireColumn.Hidden = True
            End Select
        Next cell
    Next ws
End Sub

The code will go to 1st sheets. Loops through all the cell in row 3. If cell value equalt to value x, y , or z (Value not letter), then it will hide the column. Once finish, it will go to next ws and repeat the process.

 

[MikeRob]

In your sample macro, i see that you loop through row 8 and when there is letter X in any column in that row, you will hide the entire column.

Now you talked about value in row 3

so it should be

Sub loopSheets()
    For Each ws In ActiveWorkbook.Worksheets
        For Each cell In ActiveWorkbook.ActiveSheet.Rows("3").Cells
            Select Case cell.Value
                Case x, y, y
                    cell.EntireColumn.Hidden = True
            End Select
        Next cell
    Next ws
End Sub

The code will go to 1st sheets. Loops through all the cell in row 3. If cell value equalt to value x, y , or z (Value not letter), then it will hide the column. Once finish, it will go to next ws and repeat the process.

ok, I tried this but to no avail. It's just spinning. I don't know if taking a while because of over 400 sheets to go through or what. Also, there was some trouble the coding was giving me because of a spaced word. Yes, the letters were just examples of values and Row 8 was a typo... It's Row 3. Here's what I'm using now:

Sub loopSheets()
    For Each ws In ActiveWorkbook.Worksheets
        For Each cell In ActiveWorkbook.ActiveSheet.Rows("3").Cells
            Select Case cell.Value
                Case IVO, Military, Provisional, Absent, Retired People
                    cell.EntireColumn.Hidden = True
            End Select
        Next cell
    Next ws
End Sub

where it says Retired People it gives an error but when I join the words it does not. Otherwise ive just been stuck at this point

 

[MikeRob]

This works for sure but not for every sheet in the workbook simultaneously, only on the active sheet I'm working on

Sub loopSheets()
    For Each ws In ActiveWorkbook.Worksheets
        For Each cell In ActiveWorkbook.ActiveSheet.Rows("8").Cells
           If cell.Value = "X" Then
               cell.EntireColumn.Hidden = True
           End If
        Next cell
    Next ws
End Sub

 

[Fluff]

How about

Sub MikeRob()
   Dim Ws As Worksheet
   Dim Cl As Range
   
   For Each Ws In ActiveWorkbook.Worksheets
      For Each Cl In Intersect(Ws.UsedRange, Ws.Rows(3))
         Select Case Cl.Value
            Case "IVO", "Military", "Provisional", "Absent", "Retired People"
               Cl.EntireColumn.Hidden = True
         End Select
      Next Cl
   Next Ws
End Sub

 

[MikeRob]

How about

Sub MikeRob()
   Dim Ws As Worksheet
   Dim Cl As Range
 
   For Each Ws In ActiveWorkbook.Worksheets
      For Each Cl In Intersect(Ws.UsedRange, Ws.Rows(3))
         Select Case Cl.Value
            Case "IVO", "Military", "Provisional", "Absent", "Retired People"
               Cl.EntireColumn.Hidden = True
         End Select
      Next Cl
   Next Ws
End Sub

ok, I tried this but to no avail. It's just spinning. I don't know if taking a while because of over 400 sheets to go through or what. Also, there was some trouble the coding was giving me because of a spaced word. Yes, the letters were just examples of values and Row 8 was a typo... It's Row 3. Here's what I'm using now:

CODE=vba]
Sub loopSheets()
For Each ws In ActiveWorkbook.Worksheets
For Each cell In ActiveWorkbook.ActiveSheet.Rows("3").Cells
Select Case cell.Value
Case IVO, Military, Provisional, Absent, Retired People
cell.EntireColumn.Hidden = True
End Select
Next cell
Next ws
End Sub

where it says Retired People it gives an error but when I join the words it does not. Otherwise ive just been stuck at this point

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

 

[MikeRob]

How about

Sub MikeRob()
   Dim Ws As Worksheet
   Dim Cl As Range
  
   For Each Ws In ActiveWorkbook.Worksheets
      For Each Cl In Intersect(Ws.UsedRange, Ws.Rows(3))
         Select Case Cl.Value
            Case "IVO", "Military", "Provisional", "Absent", "Retired People"
               Cl.EntireColumn.Hidden = True
         End Select
      Next Cl
   Next Ws
End Sub

sorry, I'm just now seeing your response but I've found a resolution now. credit is given to @Zot if you see the reply to the last post in this thread you'll see when we came up with to solve this. But I still thank you for your contribution.