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