VBA Hide columns based on value

[phillipcook]

Hi Everyone

I am trying to make excel hide or un-hide columns based on specific values. I have tried a few different macros, but just cant seem to get it right. What I am trying to achieve is as below:

Columns in range are J:P.

1) if cell E4 = "Site 2" then column J will show, if cell E4 is blank or has any other value, then J will hide

2) if cell A10 = "SW - Monthly" then column K will show, if cell is blank or has other value then K will hide.

3) If Cell A10 = "SW - Investigation" then column L will show, if cell is blank or has other value than L will hide

4) If Cell A10 = "PW - Investigation" then column M will show, if cell is blank or has other value than M will hide

5) If Cell A10 = "GW - Investigation" then column N:O will show, if cell is blank or has other value than N:O will hide

6) If Cell A10 = "DW - Daily" or "DW - Weekly" or "DW - Investigation" then column P will show, if cell is blank or has other value than P will hide

7) I need to repeat parts 2 - 6 from A10 to A19. Not sure if in the code it can look for the specific value between A10:A19 or if we have to do the code for each cell.


I've been trying to work this one out for days, but my VBA/ code/ Macro side of excel isn't my strong point.

Thanks heaps in advance!!!

 

[steve the fish]

Re: Help!! VBA Hide columns based on value

Do the cells have formulas or do you type into them?

 

[phillipcook]

Re: Help!! VBA Hide columns based on value

Do the cells have formulas or do you type into them?

Cells A10:A19 are drop down lists, but they have the ability for the user to type custom names into them.

E4 is also a list, however it doesn't have the ability to be customized. its locked to "site 1", "site 2" and "site 3"

 

[steve the fish]

Re: Help!! VBA Hide columns based on value

Sorry got tied up. See if this works:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range

If Target.Cells.CountLarge > 1 Then Exit Sub
Set rng = Intersect(Target, Union(Range("E4"), Range("A10:A19")))
If rng Is Nothing Then Exit Sub

Select Case rng.Address(0, 0)
    Case "E4"
        Columns("J").EntireColumn.Hidden = Not CBool(Target.Value = "Site 2")
    Case Else
        Columns("K").EntireColumn.Hidden = Not CBool(Target.Value = "SW - Monthly")
        Columns("L").EntireColumn.Hidden = Not CBool(Target.Value = "SW - Investigation")
        Columns("M").EntireColumn.Hidden = Not CBool(Target.Value = "PW - Investigation")
        Columns("N:O").EntireColumn.Hidden = Not CBool(Target.Value = "GW - Investigation")
        Columns("P").EntireColumn.Hidden = Not CBool(Target.Value = "DW - Daily" Or Target.Value = "DW - Weekly" Or Target.Value = "DW - Investigation")
End Select

End Sub

 

[phillipcook]

Re: Help!! VBA Hide columns based on value

Sorry got tied up. See if this works:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range

If Target.Cells.CountLarge > 1 Then Exit Sub
Set rng = Intersect(Target, Union(Range("E4"), Range("A10:A19")))
If rng Is Nothing Then Exit Sub

Select Case rng.Address(0, 0)
    Case "E4"
        Columns("J").EntireColumn.Hidden = Not CBool(Target.Value = "Site 2")
    Case Else
        Columns("K").EntireColumn.Hidden = Not CBool(Target.Value = "SW - Monthly")
        Columns("L").EntireColumn.Hidden = Not CBool(Target.Value = "SW - Investigation")
        Columns("M").EntireColumn.Hidden = Not CBool(Target.Value = "PW - Investigation")
        Columns("N:O").EntireColumn.Hidden = Not CBool(Target.Value = "GW - Investigation")
        Columns("P").EntireColumn.Hidden = Not CBool(Target.Value = "DW - Daily" Or Target.Value = "DW - Weekly" Or Target.Value = "DW - Investigation")
End Select

End Sub

Sorry for the late reply (different time zones)

The code is close to being correct.

I gave it a test and the correct column will show up if the specific name is selected as per the code, however, if I have multiple names that match the criteria between A10 and A19 (eg: SW - Monthly in A10 and DW - Daily in A11), what is happening is the last entered specific match (regardless of where it is between A10:A19) will become the only un-hidden column (where in this case there should be two columns that un-hide)

Hope that make sense

Thank you for getting back to me.