Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 41
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 41
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I sort the data in 8 columns by two of the columns? Sorting Excel Worksheet Functions 1 October 25th 05 03:57 PM
How to swap rows and columns? [email protected] Excel Discussion (Misc queries) 5 September 21st 05 08:07 AM
Hiding columns and custom views problem Bettergains Excel Discussion (Misc queries) 2 April 12th 05 11:48 PM
Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns foofoo Excel Discussion (Misc queries) 1 April 2nd 05 12:02 AM
Counting the Contents of Two Columns Molochi Excel Discussion (Misc queries) 6 December 22nd 04 09:13 PM


All times are GMT +1. The time now is 06:04 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"