Hide / unhide range of columns with one click

Topic

New Reply

On my spreadsheet, I record annual leave, sick absence, training away-days etc for a whole department. I would like to be able to show only the columns with for example an ‘AL’ for annual leave on some given row, so I can easily see which dates are holiday for that person (row). This should work just like filtering rows on some value in one column. Excel won’t do this by itself, and copying, transposing and filtering is too clumsy. Any help TIA
Frank

Reply | Quote

Replies

Can you post a sample sheet? Use fake names.

Reply | Quote

Frank,

How about this?

Edit: This could be modified to request the value to filter on so you could select SL, HL, Etc.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hello Retiredgeek, this almost works – the trouble is where the range of columns is being counted. It works perfectly for Moe, as he is on holiday on day 10. For other staff, the rows up to the last AL are hidden if blank, but rows after that seem not to be selected. So for example days 3, 5, 7, 8, 9,10 remain un-hidden for Manny (with day 7 being his last AL day).

My spreadsheet has a set of columns for name, room number, extension, work team etc etc., then columns are labeled for every day of the year, cells being completed for annual leave, sick absence, study days, trips out of office and a dozen more, with a 2-letter code for each, ie AL. SK, SD and so on.

I’m looking to choose one such code, maybe SK for sick absence, then just hide all the other ‘day of the year’ columns, so I can see all the dates when that person is sick and record them for HR to have.

The column range will be the same in every case, Jan 1 is column AA and Dec 31 is (I think) OA or OB in a leap year.

This kind of thing might be really useful for a lot of people, I can think of several other applications in my job.

Many thanks again. Frank

Reply | Quote

This isn’t a complete solution, but a technique that may be useful.

Put a formula in, say, row 1 that returns an error if that particular column should be hidden. If you do that, then you just need a couple of lines of code, I think:

Cells.EntireColumn.Hidden = False
Rows(“1:1”).SpecialCells(xlCellTypeFormulas, xlErrors).EntireColumn.Hidden = True

Not sure that I follow exactly what’s needed, but it sounds like the OFFSET() function would probably be useful in the formula – to return #N/A if the applicable cell below contains “AL” ??

Reply | Quote

Frank,

Ok I think this fixes it.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Just took another look at this. Attachment illustrates what I meant in my earlier post – about using a formula to drive whether or not the column is hidden. The reason for using #N/A, by the way, is that then you can use the xlErrors parameter of the SpecialCells function.
In the attached I made it that clearing inpCode (cell C7) will cause all columns to be unhidden.

Reply | Quote

Hello Colin and RG,
I’ve not been able to make either of your suggestions work yet, but don’t fully grok the code. I’m spending a bit of time this evening on it and will report back with news. Many thanks for all you help. Frank

Reply | Quote

Frank,

Here’s a modified version that picks up the value in the selected cell as the filter value so it will work for any type of leave.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

On my spreadsheet, I record annual leave, sick absence, training away-days etc for a whole department. I would like to be able to show only the columns with for example an ‘AL’ for annual leave on some given row, so I can easily see which dates are holiday for that person (row). This should work just like filtering rows on some value in one column. Excel won’t do this by itself, and copying, transposing and filtering is too clumsy. Any help TIA
Frank

Have you investigated Custom Views, in 2007 they are on the View Ribbon. Does what I think you want without any code.

Reply | Quote

Hello Catherine, thanks for this but custom views won’t do the job. They only save a pre-chozen set of show/hide columns and I need to have excel choose which ones to hide according to changing criteria.
I’m still trying with the suggestions from RG and may have time to try again over the weekend. Thanks Frank

Reply | Quote

Hi Frank,
I’m curious as to what didn’t work with the attachment in my last post (Attendance.xlsm). I noticed that I saved it as an Excel 2007 workbook, but presumbaly you would have said if that was the problem.
Quite often something can be [primarily] accomplished with either Excel formulas or with VBA code. I tend to favour the former, since it’s generally more maintainable/understandable to end users. And also more visible, of course.
Anyway, what I posted *does* have a kind of funky formula in the row at the top. It’s designed to return #N/A if that particular column should be hidden. The VBA code itself is then very short (and runs quickly), since it’s just initially unhiding all the columns and then hiding just those columns where the aforementioned formula evaluates to #N/A. I say quicker because it does the hiding all at once – without having to iterate through the columns (no offense, RG!).
Colin

Reply | Quote

Colin,

None Taken. Another view is always welcome!

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hello Colin,
I can see how your solution works now. I was not able to unhide all columns after using the refresh button, by (I thought) keeping the name field blank – hoping to restore the sheet to it’s original state.
I can always just close without saving of course. One useful feature is that the columns either side of the dated columns block are unaffected – I shall shortly upload a dummey example showing why this is good.

Wishing you all a happy holiday season.
Frank

Reply | Quote