Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hiding rows or columns based on cell contents | Excel Discussion (Misc queries) | |||
Hiding columns based on row 4 criteria | Excel Worksheet Functions | |||
Hiding Columns based on value from another worksheet | Excel Programming | |||
Hiding columns based upon a value (or vice versa) | Excel Programming | |||
Hiding columns based on user/password | Excel Worksheet Functions |