Hiding columns based on value
 

Please help, Ive 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 Im 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 cant 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

Hiding columns based on value
 

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" wrote in message
...
Please help, Ive 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 Im 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 cant 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

Hiding columns based on value
 

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

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
Please help, Ive 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 Im 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 cant 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

Hiding columns based on value
 

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

Hiding columns based on value
 

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" wrote in message
...
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

Hiding columns based on value
 

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

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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

Hiding columns based on value
 

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)" wrote:

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" wrote in message
...
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

Hiding columns based on value
 

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" wrote in message
...
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)" wrote:

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" wrote in message
...
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

Hiding columns based on value
 

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)" wrote:

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" wrote in message
...
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)" wrote:

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" wrote in message
...
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

Hiding columns based on value
 

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


"turbogreg17" wrote in message
...
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)" wrote:

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" wrote in message
...
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)" wrote:

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" wrote in message
...
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