Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hiding/Unhiding Columns
I want to hide or unhide columns if the value in the bottom row of the column
is zero. This may change when the number in another cell is changed. Is this possible? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hiding/Unhiding Columns
'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range) '----------------------------------------------------------------- Columns(8).Hidden = Cells(Rows.Count, 8).End(xlUp).Value = 0 End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "xkarenxxxx" wrote in message ... I want to hide or unhide columns if the value in the bottom row of the column is zero. This may change when the number in another cell is changed. Is this possible? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hiding/Unhiding Columns
Can you tell me what the significance of the (8) is, so I can understand what
the code is doing? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hiding/Unhiding Columns
Columns(8) is column H
You could also type: Columns("H") for better readability (but poor performance) HTH -- AP "xkarenxxxx" a écrit dans le message de news: ... Can you tell me what the significance of the (8) is, so I can understand what the code is doing? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hiding/Unhiding Columns
I'm not getting this to work. I want to hide any columns in the range of F
to S if the value in row 38 of the column is zero. This can change from zero when the value in cell B5 is changed "Ardus Petus" wrote: Columns(8) is column H You could also type: Columns("H") for better readability (but poor performance) HTH -- AP "xkarenxxxx" a écrit dans le message de news: ... Can you tell me what the significance of the (8) is, so I can understand what the code is doing? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hiding/Unhiding Columns
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lCol As Long If Intersect(Target, Range("B5")) Is Nothing Then Exit Sub For lCol = Columns("F").Column To Columns("S").Column With Cells(38, lCol) .EntireColumn.Hidden = (.Value = 0) End With Next lCol End Sub HTH -- AP "xkarenxxxx" a écrit dans le message de news: ... I'm not getting this to work. I want to hide any columns in the range of F to S if the value in row 38 of the column is zero. This can change from zero when the value in cell B5 is changed "Ardus Petus" wrote: Columns(8) is column H You could also type: Columns("H") for better readability (but poor performance) HTH -- AP "xkarenxxxx" a écrit dans le message de news: ... Can you tell me what the significance of the (8) is, so I can understand what the code is doing? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hiding/Unhiding Columns
Brilliant, works perfectly, thank you very much!
"Ardus Petus" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Dim lCol As Long If Intersect(Target, Range("B5")) Is Nothing Then Exit Sub For lCol = Columns("F").Column To Columns("S").Column With Cells(38, lCol) .EntireColumn.Hidden = (.Value = 0) End With Next lCol End Sub HTH -- AP "xkarenxxxx" a écrit dans le message de news: ... I'm not getting this to work. I want to hide any columns in the range of F to S if the value in row 38 of the column is zero. This can change from zero when the value in cell B5 is changed "Ardus Petus" wrote: Columns(8) is column H You could also type: Columns("H") for better readability (but poor performance) HTH -- AP "xkarenxxxx" a écrit dans le message de news: ... Can you tell me what the significance of the (8) is, so I can understand what the code is doing? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hiding/Unhiding Columns
It helps if you give full details at the beginning.
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "xkarenxxxx" wrote in message ... Brilliant, works perfectly, thank you very much! "Ardus Petus" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Dim lCol As Long If Intersect(Target, Range("B5")) Is Nothing Then Exit Sub For lCol = Columns("F").Column To Columns("S").Column With Cells(38, lCol) .EntireColumn.Hidden = (.Value = 0) End With Next lCol End Sub HTH -- AP "xkarenxxxx" a écrit dans le message de news: ... I'm not getting this to work. I want to hide any columns in the range of F to S if the value in row 38 of the column is zero. This can change from zero when the value in cell B5 is changed "Ardus Petus" wrote: Columns(8) is column H You could also type: Columns("H") for better readability (but poor performance) HTH -- AP "xkarenxxxx" a écrit dans le message de news: ... Can you tell me what the significance of the (8) is, so I can understand what the code is doing? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hiding/Unhiding Columns
I appreciate that now, I'll know that in future.
Thanks again for your help. Regards Karen "Bob Phillips" wrote: It helps if you give full details at the beginning. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "xkarenxxxx" wrote in message ... Brilliant, works perfectly, thank you very much! "Ardus Petus" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Dim lCol As Long If Intersect(Target, Range("B5")) Is Nothing Then Exit Sub For lCol = Columns("F").Column To Columns("S").Column With Cells(38, lCol) .EntireColumn.Hidden = (.Value = 0) End With Next lCol End Sub HTH -- AP "xkarenxxxx" a écrit dans le message de news: ... I'm not getting this to work. I want to hide any columns in the range of F to S if the value in row 38 of the column is zero. This can change from zero when the value in cell B5 is changed "Ardus Petus" wrote: Columns(8) is column H You could also type: Columns("H") for better readability (but poor performance) HTH -- AP "xkarenxxxx" a écrit dans le message de news: ... Can you tell me what the significance of the (8) is, so I can understand what the code is doing? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I sort the data in 8 columns by two of the columns? | Excel Worksheet Functions | |||
How to swap rows and columns? | Excel Discussion (Misc queries) | |||
Hiding columns and custom views problem | Excel Discussion (Misc queries) | |||
Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) | |||
Counting the Contents of Two Columns | Excel Discussion (Misc queries) |