Viewing Specific Columns at Various Times

  • Thread starter Thread starter John13
  • Start date Start date
J

John13

I have a spreadsheet that has accounts in Column A and data in some of
the rest of the columns. For example Columns B through M are month
columns and Column N is Year Total. Then Columns U through AF are Year-
to-Date by month and Column AG is Total Year Y-T-D.

When reviewing January, I want to see only Columns B, N, O & U and
when reviewing February I want to see only Columns C, N, O & V.

Is there a way to group these Columns with a simple process? Thank
you.

John
 
John

Check out View>Custom Views.

Name one January and the other February.


Gord Dibben MS Excel MVP
 
You could use a case select within a worksheet_event such as the selection
event so that if you select any cell in the Jan column all are hidden except
those desired. Feb, different set.
 
Another way is to use a macro.

This asks you for a number 1=Jan, ..., 12=Dec. Then hides/shows what you ask
for:

Option Explicit
Sub testme()
Dim myMonth As Long
Dim myRng As Range

myMonth = Application.InputBox(Prompt:="Number of the month", _
Default:=Month(Date), Type:=1)

With ActiveSheet
Select Case myMonth
Case Is < 1, Is > 12
.UsedRange.Columns.Hidden = False
Case Is <= 12
.UsedRange.Columns.Hidden = True
Set myRng = Union(.Range("A1").Offset(0, myMonth), _
.Range("a1").Offset(0, 19 + myMonth), _
.Range("n1:o1"))
myRng.EntireColumn.Hidden = False
End Select
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Another way is to use a macro.

This asks you for a number 1=Jan, ..., 12=Dec. Then hides/shows what you ask
for:

Option Explicit
Sub testme()
Dim myMonth As Long
Dim myRng As Range

myMonth = Application.InputBox(Prompt:="Number of the month", _
Default:=Month(Date), Type:=1)

With ActiveSheet
Select Case myMonth
Case Is < 1, Is > 12
.UsedRange.Columns.Hidden = False
Case Is <= 12
.UsedRange.Columns.Hidden = True
Set myRng = Union(.Range("A1").Offset(0, myMonth), _
.Range("a1").Offset(0, 19 + myMonth), _
.Range("n1:o1"))
myRng.EntireColumn.Hidden = False
End Select
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:http://www.mvps.org/dmcritchie/excel/getstarted.htm
Click to expand...

Thank you all for your answers. You have been very helpful and I
appreciate the learning experience.

John
 
Back
Top