Hide Columns not Matching Reference Cell?

Hi all!

Thanks for taking the time to read this. Needing the help of a Macro wizard!

I'm a newbie at Macros but very keen to learn. I want to create a Macro to hide a set range of columns that do not contain a matching reference to cell (say A1).

Cell A1 will contain a text reference which will vary and this will be referenced on the same row in each column (example attached).

Could someone help me write this Macro, and if willing, explain the commands line by line (or anything else you think I should know!).

I would be very grateful!!!

Regards,

Coeus.

Something like this should do - I've put in comments, but feel free to ask if you have questions:

To insert this macro:

1. With your workbook open (and it will be easiest if you only have one workbook open) press Alt-F11 to open the VB editor
2. In the panel on your left will a list of the sheets in your workbook - double click on the sheet that contains your data
3. A blank text editor window will open
4. In the left-hand drop-down above that window select "Workbook"
5. In the right-hand drop-down select "Change"
6. The first and last lines of the macro below will be automatically inserted, paste the rest of my code in between them
7. Close the VB editor (red cross at the top right)
8. You will no longer be able to save your workbook as a *.xlsx file, so save it as a *.xlsm (macro enabled workbook)
9. Give it a whirl and let me know how it goes

Please Login or Register  to view this content.

Here is a simple one for you. It could be more efficient (which would actually matter if you had a lot more columns), but it's good for explaining things. It should be run while the active sheet is the one you want to apply it to. Have fun learning!

Also read John Walkenbach's book titled "VBA programming for dummies". It's amazing!

Please Login or Register  to view this content.

Holy poo on a stick that Macro was amazing Andrew-R!

Not only how it works but the time it took you to compile it outstands me. Easy 2 hours work and research for me to achieve this!

I'm going to have to go through each line and probably Google it to be able to understand and replicate but it works perfectly!

Many many thanks to you, Mike and Gergo.

Is John Walkenbach's book titled "VBA programming for dummies" universally recommended?