Hiding columns based on value

[turbogreg17]

Please help, I’ve searched for the answer, but my problem seems to be unique.
Here is the background: I have a spreadsheet with many, many columns of data
(“E†through “EO†as of today, and it keeps growing). I have 9 rows of data
(rows 3 -12), so the area I’m concerned with as of today spans E3:EO12. The
cells are either blank or contain a “X†(just two choices).
I want to hide every column that contains an “X†in each of its rows (i.e.
if every cell in “E3:E12†contains “Xâ€, then hide “Eâ€, same for column “Fâ€,
“Gâ€, etc.). If any one of the rows in a column is blank, I need to see the
column. I just can’t get the syntax right for the macro.
I thought about redoing the macro code for each column, but there has to be
a better way. And it would be a bit cumbersome as I continue to add columns.
Can someone please help? Thanks. Greg

 

[Rick Rothstein \(MVP - VB\)]

Did you want the hiding action to be dynamic (in other words, as soon as the
completing 'X' is placed)?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long
Dim Contents As String
With Target
If Not Intersect(Target, Range("E3:EO12")) Is Nothing Then
For X = 3 To 12
Contents = Contents & Cells(X, .Column).Value
Next
If UCase(Contents) = "XXXXXXXXXX" Then .EntireColumn.Hidden = True
End If
End With
End Sub

Or did you want it as a "when I activate it" type macro?

Sub HideColumns()
Dim X As Long, Z As Long
Dim Contents As String
For Z = 5 To 145
Contents = ""
For X = 3 To 12
Contents = Contents & Cells(X, Z).Value
Next
If UCase(Contents) = "XXXXXXXXXX" Then
Cells(1, Z).EntireColumn.Hidden = True
End If
Next
End Sub

Rick

 

[turbogreg17]

Rick!! Thanks!! Perfect! I'll try both...plus I learned some new tricks.
Been a long time since I messed with VB. Thanks again! Greg

 

[turbogreg17]

Rick,

Is there a way we can get your code to look at each column in the sheet and
"run until done". My plan was to tweak the code each time more columns were
added. I'm sure there is a way to look at row 2 (my heading row) to see if
new data (in other words a new column which may contain blanks or "x's" has
beed added. Thanks in advance. Greg

 

[Rick Rothstein \(MVP - VB\)]

You didn't say which of the two routines I posted was the one you planned to
use, so here are both of them modified to run to the last filled in column
in Row 2...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long
Dim LC As Long
Dim Contents As String
' Find last used column in Row 2
LC = Cells(2, Columns.Count).End(xlToLeft).Column
With Target
If Not Intersect(Target, Range("E3", Cells(12, LC))) Is Nothing Then
For X = 3 To 12
Contents = Contents & Cells(X, .Column).Value
Next
If UCase(Contents) = "XXXXXXXXXX" Then .EntireColumn.Hidden = True
End If
End With
End Sub

Sub HideColumns()
Dim X As Long, Z As Long
Dim LastColumn As Long
Dim Contents As String
LastColumn = Cells(2, Columns.Count).End(xlToLeft).Column
For Z = 5 To LastColumn
Contents = ""
For X = 3 To 12
Contents = Contents & Cells(X, Z).Value
Next
If UCase(Contents) = "XXXXXXXXXX" Then
Cells(1, Z).EntireColumn.Hidden = True
End If
Next
End Sub

Rick

 

[turbogreg17]

Thanks Rick, I haven't figured out which routine best meets my needs; I think
it will be on macro run. But thanks GREATLY for providing both. You made
this easy. Greg

 

[turbogreg17]

Rick, please help me get smarter (and let's just focus on the "on run"
macro). Let's say I wanted to add a third catagory such as "NA" to the mix
(i.e., Blank, "X", and "N/A").
Would the syntax for the "If UCase(Contents)...Then" line read...If
UCase(Contents) = "XXXXXXXXXX" 0r “N/AN/AN/AN/AN/AN/AN/AN/AN/AN/A†Then ?
(listing "N/A" tne times). (I tried it with tthat experimental syntax, but
it didn't work). Thanks for your patience and leason. Greg

 

[Rick Rothstein \(MVP - VB\)]

I would think these would do what you want...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long
Dim LC As Long
Dim Contents As String
' Find last used column in Row 2
LC = Cells(2, Columns.Count).End(xlToLeft).Column
With Target
If Not Intersect(Target, Range("E3", Cells(12, LC))) Is Nothing Then
For X = 3 To 12
Contents = Contents & Cells(X, .Column).Value
Next
If UCase(Contents) = "XXXXXXXXXX" Or _
UCase(Contents) = "N/AN/AN/AN/AN/AN/AN/AN/A" Then
.EntireColumn.Hidden = True
End If
End If
End With
End Sub

Sub HideColumns()
Dim X As Long, Z As Long
Dim LastColumn As Long
Dim Contents As String
LastColumn = Cells(2, Columns.Count).End(xlToLeft).Column
For Z = 5 To LastColumn
Contents = ""
For X = 3 To 12
Contents = Contents & Cells(X, Z).Value
Next
If UCase(Contents) = "XXXXXXXXXX" Or _
UCase(Contents) = "N/AN/AN/AN/AN/AN/AN/AN/A" Then
Cells(1, Z).EntireColumn.Hidden = True
End If
Next
End Sub

Rick

 

[turbogreg17]

Oh, I see, I have to repeat the UCase command and I notice the underscore
next to the "or". Thanks very much! Greg

 

[Rick Rothstein \(MVP - VB\)]

The underscore next to the "Or" is because I continued the single statement
onto the next line... I did that so your newsreader wouldn't break the line
at a "bad" location. To use a different statement to illustrate the
continuation character (a space followed by an underscore at the end of a
line)...

Debug.Print "Line one" & vbCrLf & "Line two"

The above line will print "Line one" (without the quotes) on one line and
print "Line two" on the next line. The following statement is identical to
the above line (not just similar and not just functionally equivalent, they
are identical... the continuation character just gives you the ability to
"format" your statement to fit neater on your display)....

Debug.Print "Line one" & vbCrLf & _
"Line two"

One note... you cannot use the continuation character inside of a string
constant (directly quoted text). So, the following is NOT allowed...

Debug.Print "Line one _
Line two"

Rick