Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hiding/Unhiding Columns
My spreadsheet has numerous sections all with period numbers 1-12 showing in
columns on line 6. Line 6 also contains some blank cells and text cells. Cell D1 contains the current period number. I want to be able to hide any columns where the number in line 6 is greater than the value in cell D1 ie if D1 is 4, then any columns in the spreadsheet that contain a number greater than 4 in line 6 would be hidden. Can anyone help please. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hiding/Unhiding Columns
Dim cell As Range On Error Resume Next For Each cell In Rows(6).Cells cell.EntireColumn.Hidden = cell.Value Range("D1").Value Next cell -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Karen McKenzie" wrote in message ... My spreadsheet has numerous sections all with period numbers 1-12 showing in columns on line 6. Line 6 also contains some blank cells and text cells. Cell D1 contains the current period number. I want to be able to hide any columns where the number in line 6 is greater than the value in cell D1 ie if D1 is 4, then any columns in the spreadsheet that contain a number greater than 4 in line 6 would be hidden. Can anyone help please. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hiding/Unhiding Columns
Thanks Bob,
Can I get this to run automatically when the value in cell D1 changes? "Bob Phillips" wrote: Dim cell As Range On Error Resume Next For Each cell In Rows(6).Cells cell.EntireColumn.Hidden = cell.Value Range("D1").Value Next cell -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Karen McKenzie" wrote in message ... My spreadsheet has numerous sections all with period numbers 1-12 showing in columns on line 6. Line 6 also contains some blank cells and text cells. Cell D1 contains the current period number. I want to be able to hide any columns where the number in line 6 is greater than the value in cell D1 ie if D1 is 4, then any columns in the spreadsheet that contain a number greater than 4 in line 6 would be hidden. Can anyone help please. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hiding/Unhiding Columns
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "D1" Dim cell As Range On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target For Each cell In Rows(6).Cells If cell.Value < "" And IsNumeric(cell.Value) Then cell.EntireColumn.Hidden = cell.Value .Value End If Next cell End With End If ws_exit: Application.EnableEvents = True 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) "Karen McKenzie" wrote in message ... Thanks Bob, Can I get this to run automatically when the value in cell D1 changes? "Bob Phillips" wrote: Dim cell As Range On Error Resume Next For Each cell In Rows(6).Cells cell.EntireColumn.Hidden = cell.Value Range("D1").Value Next cell -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Karen McKenzie" wrote in message ... My spreadsheet has numerous sections all with period numbers 1-12 showing in columns on line 6. Line 6 also contains some blank cells and text cells. Cell D1 contains the current period number. I want to be able to hide any columns where the number in line 6 is greater than the value in cell D1 ie if D1 is 4, then any columns in the spreadsheet that contain a number greater than 4 in line 6 would be hidden. Can anyone help please. |
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) |