making only specified columns visible
 

I am trying to create a macro that will allow the user of a spreadsheet
to only view specified columns. It is easier to describe using an
example:

1/05 1/05 2/05 2/05 3/05

matt 2 4 6 5 3
chris 3 4 2 7 1
sara 1 1 9 8 5
ann 5 4 3 3 6

If the user only wants to see data for the month of Feb., is there a
way to have a macro hide all columns except those with a column heading
of 2/05? Thank you for your help in advance.

The following code goes in the sheet module.

It assumes the column headings are in row 4...
(Target.Row =4...ElseIf Target.Row < 4)

To only show a particular month columns, double click that month in the heading row.
To show all columns - double click above the columns heading row.

'-----------------------------------------------------
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Row = 4 Then
Dim rngRow As Excel.Range
Dim rngCell As Excel.Range
Set rngRow = Application.Intersect(Target.EntireRow, Me.UsedRange)

For Each rngCell In rngRow.Cells
If rngCell.Value = Target.Value Then
rngCell.EntireColumn.Hidden = False
Else
rngCell.EntireColumn.Hidden = True
End If
Next 'rngcell
Set rngCell = Nothing
Set rngRow = Nothing
Cancel = True
ElseIf Target.Row < 4 Then
Me.UsedRange.EntireColumn.Hidden = False
Cancel = True
End If
End Sub
'---------------------------------------

Jim Cone
San Francisco, USA



wrote in message
oups.com...
I am trying to create a macro that will allow the user of a spreadsheet
to only view specified columns. It is easier to describe using an
example:

1/05 1/05 2/05 2/05 3/05

matt 2 4 6 5 3
chris 3 4 2 7 1
sara 1 1 9 8 5
ann 5 4 3 3 6

If the user only wants to see data for the month of Feb., is there a
way to have a macro hide all columns except those with a column heading
of 2/05? Thank you for your help in advance.