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

darth_chunk · Oct 13th 2017

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;

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


If Target.Column = 1 And Target.Row = 1 And Target.Value = "Feb" Then
Range("B:AF").EntireColumn.Hidden = True
Range("BI:NH").EntireColumn.Hidden = True
Else
Range("B:AF").EntireColumn.Hidden = False
Range("BI:NH").EntireColumn.Hidden = False
End If


If Target.Column = 1 And Target.Row = 1 And Target.Value = "Mar" Then
Range("B:BH").EntireColumn.Hidden = True
Range("CN:NH").EntireColumn.Hidden = True
Else
Range("B:BH").EntireColumn.Hidden = False
Range("CN:NH").EntireColumn.Hidden = False
End If


If Target.Column = 1 And Target.Row = 2 And Target.Value = "Captain America" Then
Range("5:50").EntireRow.Hidden = True
Else
Range("5:50").EntireRow.Hidden = False
End If


If Target.Column = 1 And Target.Row = 2 And Target.Value = "The Hulk" Then
Range("6:50").EntireRow.Hidden = True
Else
Range("6:50").EntireRow.Hidden = False
End If
End Sub

Display More

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

Mumps · Oct 13th 2017

Try this macro:

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1:A2")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim rng As Range
    If Target.Row = 1 Then
        Columns.EntireColumn.Hidden = False
        For Each rng In Range(Cells(1, 2), Cells(1, Cells(1, Columns.Count).End(xlToLeft).Column))
            If Format(rng, "mmm") <> Target Then
                Cells(1, rng.Column).EntireColumn.Hidden = True
            Else
                Cells(1, rng.Column).EntireColumn.Hidden = False
            End If
        Next rng
    ElseIf Target.Row = 2 Then
        Range("A1:NH" & Range("A" & Rows.Count).End(xlUp).Row).AutoFilter Field:=1, Criteria1:=Target
    End If
    For Each rng In Range(Cells(1, 2), Cells(1, Cells(1, Columns.Count).End(xlToLeft).Column))
        If rng.Column <> 2 Then
            rng.AutoFilter Field:=rng.Column, Visibledropdown:=False
        End If
    Next rng
End Sub

Display More

**Carim** · Oct 13th 2017

Hi,

Attached is your workbook ... with a streamlined macro ...

Please carry on your tests ... to find out if you getting your expected outcome ... :wink:

Hope this will help

darth_chunk · Oct 13th 2017

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:

**Carim** · Oct 13th 2017

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 ....

**Carim** · Oct 13th 2017

Hello again Joel,

Attached is the Version 2 .. which hopefully ... will be in line with your expectations ...:wink:

Hope this will help ...

**Carim** · Oct 17th 2017

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

darth_chunk · Oct 18th 2017

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.

**royUK** · Oct 18th 2017

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

Code

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range
    Dim lRw As Long, lCol As Long


    With Target
        If .Count > 1 Then Exit Sub
        If Intersect(Target, Range("A1:A2")) Is Nothing Then Exit Sub


        lRw = Range("A1").CurrentRegion.Rows.Count
        lCol = Range("A1").CurrentRegion.Columns.Count


        Application.ScreenUpdating = False


        Select Case Target.Address
        Case "$A$1"
            If .Value = "Select Date" Then
                Range(Cells(1, 2), Cells(1, lCol)).EntireColumn.Hidden = False
                Exit Sub
            End If


            For Each c In Range(Cells(1, 2), Cells(1, lCol)).Cells
                c.EntireColumn.Hidden = Not CStr(Format(c.Value, "mmm")) = .Value
            Next c


        Case "$A$2"
            If .Value = "Select SuperHero" Then
                Range(Cells(4, 1), Cells(lRw, 1)).EntireRow.Hidden = False
                Exit Sub
            End If


            For Each c In Range(Cells(4, 1), Cells(lRw, 1)).Cells
                c.EntireRow.Hidden = Not c.Value = .Value
            Next c


        End Select
    End With
    Application.ScreenUpdating = True
End Sub

Display More

darth_chunk · Oct 18th 2017

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.

Tom.Jones · Oct 18th 2017

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.

**Carim** · Oct 18th 2017

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:

darth_chunk · Oct 19th 2017

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.

Tom.Jones · Oct 19th 2017

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:

**royUK** · Oct 19th 2017

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

darth_chunk · Oct 19th 2017

@ 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 :?

Tom.Jones · Oct 19th 2017

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.

darth_chunk · Oct 20th 2017

@ 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

**royUK** · Oct 29th 2017

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

darth_chunk · Oct 30th 2017

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:

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

Participate now!

Share