Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Making list with unique columns | Excel Worksheet Functions | |||
making columns automatically turn numbers into negatives | Excel Worksheet Functions | |||
Making a One Column Directory from Two Columns | Excel Discussion (Misc queries) | |||
Columns in Excel will not allow user to click in them | Excel Discussion (Misc queries) | |||
Making Rows into Columns | Excel Discussion (Misc queries) |