Hide specific columns dependant on number in specific cell

**ladbroke** · 06-13-2012, 10:56 AM

Hi,

Can someone help me produce a macro in which when a number is chosen from a combobox (number ranges from 1-6 inclusive) that it hides specific columns (it would be about 50 columns approximately, but not necessarily side by side, they can be anything like column f, r, t, aa etc). When the number is chosen it does get put into cell A1, and remains until a new number is selected.

What I would want is for when a number is selected, it hides only those specific columns, so if a new number is selected, it makes all the columns re-appear momentarily and then hides the ones it doesn't need.

I hope I have made some sort of sense here.

**tigeravatar** · 06-13-2012, 12:38 PM

ladbroke,

For something like this, I would recommend using a data validation cell and then utilizing the worksheet_change event. If that is not possible, then are you using a form control combobox or an activex control combobox? (neither triggers the worksheet_change event)

**ladbroke** · 06-13-2012, 04:28 PM

Hi,

If its any help, I am using Excel 2003 on an old laptop - and as its my girlfriends, so updating it may not be an option.
I am also using what can only be said to be a form control combobox.
What way would you recommend then as I would prefer a drop down menu of some sort, and then the cells to hide that are not needed (well, the ones that are in use anyway)?

**tigeravatar** · 06-13-2012, 04:46 PM

ladbroke,

Attached is an example workbook based on the criteria you described. It uses a data-validation drop down list in cell A1, which is monitored by the worksheet_change event. Here is the worksheet_change event code:

Please Login or Register  to view this content.

To view the code, press Alt+F11 and double-click on Sheet1. To see how it works, you can scroll the spreadsheet left and right to see what columns get hidden based on the A1 cell value. Then change the A1 cell value and you'll see that the appropriate columns become hidden. To have all columns be shown, select cell A1 and press Delete to clear the value in A1. Note that this will not delete the drop-down list, so you could make another selection if you like.