Macro to selectively hide columns

[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

 

[Dave Hawley]

Hi Darren

Have you looked into using Grouping?

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Don Guillett]

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.