Hiding Columns based on References

Hi all,

Thanks for taking the time to read this.

My macro brain is falling a bit short on this one and would appreciate any help!

In a worksheet in row 1 from columns C to Z for example, is a set of data with a number in each column in ascending order however the same number can occur multiple times.

For example:

C D E F G H I J K L M N ...
1 1 2 2 2 3 3 3 3 4 5 5

Based on two cells in cells A1 and A2 I would like to hide columns not containing the range on numbers covered including and between those in A1 and A2.

Cell A1 would be the starting point - for example number 2.

Cell A2 would be the ending point - for example number 4.

The result would be:

E F G H I J K L
2 2 2 3 3 3 3 4


I believe the macro would be worksheet > change based on these cells.

I believe the best way to make it run is to populate cell A1 runs first, hiding everything except that number.

Then populate cell A2, unhiding from the number in A1 to the number in A2.


Im open to better ways of accomplishing this!


Any input would be greatly appreciated!


Many thanks in advance,


Coeus.

Hi Olly and GE Excel,

Many thanks for your fast replies.

I'm reading through and get the basics of these, the macro type, defining variables, for, next, if etc.

Im not sure on how 'If Not Intersect...' and subsequent parts works.

Would you mind posting or adding comments to better help me understand how this all works? I try not to blind copy any feedback otherwise how would one learn!


Again many thanks for your input,


Coeus.

Hi GC,

Just tried testing yours - it seems to hide the columns I want shown i.e. for 2 and 4 in cells A1 and A2 its only any columns with 2's, 3's and 4's I would like shown.

Would you mind re-posting / amending your macro and Ill give that a whirl!

Many thanks again,


Coeus.

One last level to add onto this...

Say I were to change cells A1 and A2 to weeks e.g. WEEK1, WEEK2, WEEK3 etc. and each reference was in the same format e.g.:

A B C D E
WEEK 1 WEEK 1 WEEK 2 WEEK 3 WEEK 4

Would you need to change 'c' variable to say the =RIGHT(range, 1) or something along those lines?


Many thanks,


Coeus.

Hi all,

I have modified the macro for the layout of the worksheet and to try to achieve the hiding desired.

Can you please review as this is not working :-(

Please Login or Register  to view this content.

The references it is benchmarking against in B7 to B8 is in the format WK01, WK02 etc. hence the RIGHT(xxx, 2) to split just the numbers out.


Again any input would be greatly appreciated!


Coeus.