• Hide / unhide range of columns with one click

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Hide / unhide range of columns with one click

    Author
    Topic
    #473247

    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

    Viewing 12 reply threads
    Author
    Replies
    • #1256591

      Can you post a sample sheet? Use fake names.

    • #1256607

      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

    • #1256672

      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

    • #1256677

      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” ??

    • #1256717

      Frank,

      Ok I think this fixes it.

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

    • #1256751

      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.

    • #1258586

      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

    • #1258606

      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

    • #1258762

      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.

    • #1259244

      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

    • #1259250

      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

    • #1259403

      Colin,

      None Taken. Another view is always welcome!

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

    • #1260081

      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

    Viewing 12 reply threads
    Reply To: Hide / unhide range of columns with one click

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: