Auto Hide Columns and/or Rows using VBA based on cell value (validation list)

  • Hi,


    I'm wondering if anyone can help me with this VBA problem I'm having.


    To lay the backdrop; my understanding of VBA is limited and I've been reading tutorials to implement the functions I want. However, when trying to stack multiple 'If' arguments the code isn't behaving the way I expected it to.


    In short, I am using VBA to auto hide columns and or rows depending on the value of a cell which has a pre-determined list using a validation drop down box. The aim of this is to hide a range of columns, or multiple ranges of columns.


    So in the attached example, "SuperHero Patrol" when selecting 'Jan' from the drop down in Cell A1, I want the sheet to auto hide all columns where the date isn't January. When selecting 'Feb' it auto hides everything not February and so on. The first time I added the code it seemed to work exactly how I wanted it to; i.e. it would auto hide all columns that weren't January. But when I add the next logic string to auto hide everything that isn't Feb it seems to not work the same way. Instead of hiding the target range, it seems to hide a completely different range. Additionally the first logic argument now seems not to work at all.


    So the initial code is as follows;


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 And Target.Row = 1 And Target.Value = "Jan" Then
    Range("AG:NH").EntireColumn.Hidden = True
    Else
    Range("AG:NH").EntireColumn.Hidden = False
    End If


    This achieved what I wanted it to which was to auto hide all columns that weren't January. However, once I expanded it as follows, the code didn't work;




    Same is true to the code to hide the rows.


    Can anyone help to explain what is wrong with the code and why it isn't behaving the way I want it to?


    Thanks :)

  • Try this macro:

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hey,


    Thanks @ Mumps and @ Carim for your help.


    @ Mumps, the code seems to do the trick for the column (months) filter I need although it takes a while to run through the process. I can't seem to get it working with the row (superhero) filter. It's also stopped working (I can't figure out why).


    @ Carim, thanks. Much cleaner than my code, but I run into the same problem. The code works up to a point but doesn't return the results I was expecting. So it works perfectly for example when I select 'march' from the list, but 'jan' doesn't work at all, and 'Feb' only hides the month of March and no others. It's baffling, it seems to work as a single code but stacking multiple variations 'breaks' it as far as I can tell.


    I am going to play around with both versions of the weekend and hopefully make some progress. I'll report back next week with results.


    Thanks again to you both for your help! :yourock:

  • Hello again,


    Sadly ... it is only with your last message that I got to understand what you are after ....:hammerhe:


    Will have a fresh start at it ....

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello Joel,


    Would very much appreciate if you could drop a message in the Forum ....:smile:


    Is your initial problem solved with the version 2 macro .. ???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi Carim,


    Apologies for my absence, away for a long weekend and then had to catch up at work.


    I've just reviewed the second version of the macro you submitted and it works perfectly - exactly as I had intended it to work. Many thanks for your assistance, it's greatly appreciated :yourock:


    Now I just have to sit and figure out how that code works. It's not something I would have ever thought to look at. Long way to go with learning VBA still it seems ^_^.


    Thanks again.

  • Here's an alternative that is more dynamic, allowing you to add rows and/or columns


  • Hi Roy,


    Thanks, I'll try that out as well. I was actually wondering how it would affect the sheet if I needed to add rows/columns to the mix, and this may do the trick :)


    I have a follow up request: feedback has been good for this spreadsheet, but I have been asked if I can make the filters more dynamic. To clarify, this is being used by colleagues of mine in our office and is not something I'm charging for or anything like that.


    Specifically, I have been asked if it is possible to be more selective in the date range to apply the filters to. I was thinking along the lines of adding a date picker with a to & from range which would then filter which columns are hidden to those specific dates.


    I have read that date pickers can be added to excel, but are restricted to 32 Bit versions of the software. Is it possible to add a date picker to the file, and could the code be altered to work with that date picker?


    I'd really love to hear people's thoughts on this.


    Thanks.

  • Hi,


    For me, neither Mumps nor royUK's code works.
    When selecting any month, all columns of data are hidden and all appear when selecting 'select data' in A1.
    royUK, can you attach a file with code to work with?
    Thank you.

  • Hello darth_chunk,


    Thanks a lot for your message ...:smile:


    Glad to hear the second version of the macro works perfectly ...wink:


    Thanks a lot ... for your Thanks ....:smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi,


    Attached is the latest version of the workbook with Carim's latest revision of the code ("SuperHero Patrol_VBA_TEST V2"), and also Roy's version of the code ("SuperHero Patrol_VBA_TEST V3"). Both work on the examples I have attached here.


    I'm just reading the examples of the date pickers Roy has linked to, looking at how I can integrate them.


    Cheers.

  • Hi,


    @ darth_chunk
    This one, SuperHero Patrol_VBA_TEST V2.xlsm(26.6 KB, 1 view), from Carim, is working very well


    I tested SuperHero Patrol_VBA_TEST V3.xlsm(25.7 KB, 1 view)and it's not working.
    If you choose any month, in A1, all columns till 'NI' column, are hidden. Have you tested?


    royUK,


    Yes, I downloaded (long time ago) that files, from links, and I used them. Thank you. :thumbcoo:

  • @ Tom.Jones


    Yes, I have tested both versions of the code on the workbooks. V3 works exactly as it should for me. When selecting any month, it auto hides all columns except for the target month. I haven't had any problems with it at all...


    Not really sure why it isn't working for you :?

  • royUK


    Quote

    To use a date range then the workbook will need some alterations. I'll look at it later


    OK. Thank you.


    @ darth chunk


    Have you tested V3 in the same file you put on the site? In columns B1: NI1, in your file it is a real date?, or are text or something else?
    Very curious...
    I have tested on multiple computers with different SO and different Office versions, and did not work. We tested the file you put on the site and added the code to Mumps and royUK.

  • @ Tom.Jones


    Yes, absolutely. The V3 file I uploaded was the one I tested & used with no issues. The date format in the cells is custom set to dd-mmm. That should be how it defaults so I can't understand why you don't see the see the same results as I do.


    Perhaps royUK can shed light on it? I'm eager to see the altered code with the date picker working anyway :)

  • Sorry, I've been rushed off my feet at work. I've also taken some time to update one of my calendar forms. It will now position itself at the cell to add the date to.


    Also, I've speeded up the hiding of columns and enabled you to display a range of dates.


    Test this

  • Hi Roy,


    Just testing out the spreadsheet now and works like a charm, thanks :) I'm sure it'll be well received!


    I noticed that merge & centre is disabled on the spreadsheet now (for good reason no doubt - as I understand it M&C can seriously screw up VBA codes). And for that reason I will be encouraging everyone who needs that aesthetic to instead use the 'centre across rows' formatting tool instead. However, I have noticed that this does not extend the formatting of the originating cell to all the others.


    Do you or does anyone else know a way around this? It's not exactly a show stopper but would be great to understand why.


    Thanks again for taking the time to help and resolve this problem, it's much appreciated :yourock:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!