Macro to selectively hide columns

  • Thread starter Thread starter stanleysi
  • Start date Start date
S

stanleysi

Hi Guys,

Am using MS Excel 2002.

Simply, I have the following columns:

May-04 | Jun-04 | Jul-04 | Aug-04 | Sep-04 | Oct-04 | Nov-04

Above this a user can choose to view the spreadsheet by any combinatio
of months (e.g.

just May-04 - hide Jun-04, Jul-04, Aug-04, Sep-04, Oct-04, Nov-04
May-04 to Jun-04 - hide Jul-04,Aug-04,Sep-04,Oct-04,Nov-04
Aug-04 to Oct-04 - hide May-04,Jun-04,Jul-04,Nov-04

Currently I am invoking a change event to tell the following macro t
hide the corresponding fields. This is done via a drop down lis
currently. Ideally, the user would be able to key in the range he want
in TWO separate list boxes. How can I modify it to do this as well?

'F2 is the value I am changing

If Range("F2").Value = "May-04" Then
Range("Q:AF").Select
Selection.EntireColumn.Hidden = False
Range("AH:AW").Select
Selection.EntireColumn.Hidden = False

Range("R:AF").Select
Selection.EntireColumn.Hidden = True
Range("AI:AW").Select
Selection.EntireColumn.Hidden = True
ElseIf Range("F2").Value = "Jun-04" Then
Range("Q:AF").Select
Selection.EntireColumn.Hidden = False
Range("AH:AW").Select
Selection.EntireColumn.Hidden = False

Range("Q:Q").Select
Selection.EntireColumn.Hidden = True
Range("AH:AH").Select
Selection.EntireColumn.Hidden = True
Range("S:AF").Select
Selection.EntireColumn.Hidden = True
Range("AJ:AW").Select
Selection.EntireColumn.Hidden = True

etc etc.

Obviously I have a HUGE sub procedure (for all the combo's from May-0
to Aug-05 and I run into a "Procedure too large" error.

Can anyone help me with this? I know there should be a way for me t
make the whole thing more flexible even if what I did worked. Pls hel
with the formula that I need.

Thanks so much!

You guys rock!

Regards,
Darre
 
Using this, the first thing you can do is get rid of all the selections.
if Range("F2").Value = "May-04" Then
Range("q:af,ah:aw").EntireColumn.Hidden = False
==
the next thing that comes to mind is that there must be some mathematical
spacing that could be used with something like an offset to really make it
very short.
 
Back
Top