Auto hide/unhide columns in excel

Hi. First thread. Ever.
I've searched high and low for coding to automatically hide/unhide columns (range = h7:av8) with a given value ("" or #N/A). This is to reduce the number of series on an associated column chart.
They all look a little like this:

Please Login or Register  to view this content.

But, alas. Not one of the "solved" suggestions have worked for me. Any tips?

Moderator Note:
Pls use code tags around your code next time as per forum rules.

Hi, BRJ,

please wrap up your procedure with code-tags for better readability.

Donīt use the Select at the end of the first codeline (will throw up an exception). Maybe itīs not working because you loop through cells in the code but you refer to Selection in the Sample:

Please Login or Register  to view this content.

Ciao,
Holger

Hi, BRJ,

maybe this may help

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

The Worksheet_SelectionChange-event I mentioned would to be placed behind the sheet you want to monitor and would be started with each movement of the cursor.

If you canīt get it to work maybe attach a sample of your workbook with no sensible data to have a look at for further help.

Ciao,
Holger

Thanks Holger.
Seems to get me a little closer to the solution I'm after. Here's a couple of points:
1. When anything changes on the worksheet, can the macro run again (ie- all the columns should be available to reappear)?
2. Ideally (I didn't mention this on my initial question), I would love the code to search H7:av7 and then h8:av8 (if blanks appear in ONLY ONE range, then the event is NOT triggered)
I know these things are a lot to ask, I don't mind if it's too much trouble for you. Thanks.

Hi, BRJ,

do the cells in the areas mentioned rely on formulas or do you enter the values by hand? That would determine if to use the Worksheet_Change-Event (manual entry or query) or Worksheet_Calculate which would be triggered by formulas and any new calculation.

If the values are entered manually:

Please Login or Register  to view this content.

Default way for a For Each...Next loop is to move right till it reaches the last column and then proceed in the next row of code with the first column.

Ciao,
Holger

Hi, BRJ,

any chance to trigger the start of the code from that combobox? The formulas could be calculated first and then evaluated from the macro. Iīm afraid there is no shortcut to cutting down the number of cells to check with formulas as I think all of the cells are formulas. Depending on the usual estimated number of columns to be shown maybe Find could be used to cut down the number of cells to loop but I would need to know what the book/sheet looks like in order to give more than just basic hints on how to proceed.

Ciao,
Holger