Use VBA to hide columns based on list

[emwhite]

With the below code (that was created by Palmetto) in this thread:
http://www.excelforum.com/excel-prog...tion-list.html

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)


    Dim bottomrow As Long
    
    bottomrow = Cells(Rows.Count, "B").End(xlUp).Row
    
    If Target.Cells.Count > 1 Then Exit Sub
    
    Application.ScreenUpdating = False
    
    With Me
        
        If Not Intersect(Target, .Range("D6")) Is Nothing Then
        
            Select Case Target.Value
                Case "All"
                    .AutoFilterMode = False
                    
                Case Is <> "All"
                    .Range("B12:B" & bottomrow).AutoFilter Field:=1, Criteria1:="=" & Target.Value, visibledropdown:=False
            End Select
        End If
    End With
    
    Application.ScreenUpdating = True

End Sub

I have very limited knowledge with VBA commands. So this is over my head.

I would like to hide Columns E-G or or H-J, depending on what is selected from the list in cell D4.

In conjunction with the above request, I would like to be able to hide columns E-J independently based on what is selected from the list in cell D5.

If this is possible, could you please point me to instructions or demonstrate it for me? Any help is greatly appreciated and I attached the sample Excel file I am working on.

[MarvinP]

Hi emwhite,
Try this code instead of the above:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim bottomrow As Long
    bottomrow = Cells(Rows.Count, "B").End(xlUp).Row
    If Target.Cells.Count > 1 Then Exit Sub
    Application.ScreenUpdating = False
    With Me
        If Not Intersect(Target, .Range("D6")) Is Nothing Then
            Select Case Target.Value
                Case "All"
                    .AutoFilterMode = False
                Case Is <> "All"
                    .Range("B12:B" & bottomrow).AutoFilter Field:=1, Criteria1:="=" & Target.Value, visibledropdown:=False
            End Select
        End If
    End With
    
    With Me
        If Not Intersect(Target, .Range("D4")) Is Nothing Then
            Columns("E:J").Hidden = False
            Select Case Target.Value
                Case "360"
                    Columns("H:J").Hidden = True
                Case "480"
                    Columns("E:G").Hidden = True
            End Select
        End If
    End With
    
    Application.ScreenUpdating = True
End Sub

[emwhite]

Hi emwhite,
Try this code instead of the above:

Hi MarvinP, thanks for the quick response!

I had to make one simple tweak from this:

Case "360"
                    Columns("E:G").Hidden = True
                Case "480"
                    Columns("H:J").Hidden = True

To this:

Case "360"
                    Columns("H:J").Hidden = True
                Case "480"
                    Columns("E:G").Hidden = True

I want to keep the values chosen from the list, to display on the screen.

Can I replicate your addition to the bottom and then change it so I can hide columns based on D5 input?

Ex:
If they choose 12 from the D5 list, it would hide columns F-G & I-J
If they choose 16 from the D5 list, it would hide columns E, G, H & J
If they choose 24 from the D5 list, it would hide columns E-F & H-I

I uploaded a new Excel file because I noticed my helper cells in row 2 had the wrong values. It also has your new code in it.

Thanks again!

[MarvinP]

Hi,

It looks like you are getting the hang of what the VBA is doing. The merged cells at just above your table will be a problem if you hide columns inside them. You can certainly add a section for the D5 cell changing.

The hint is to record a macro to do what you expect and then read the code the macro is recording to see what is happening.

I hope this helps.

[emwhite]

Hi,

It looks like you are getting the hang of what the VBA is doing. The merged cells at just above your table will be a problem if you hide columns inside them. You can certainly add a section for the D5 cell changing.

The hint is to record a macro to do what you expect and then read the code the macro is recording to see what is happening.

I hope this helps.

Thanks again for you help.

I've tried recording macros but they don't appear the same way that Palmetto and you have coded it so far. Or they ignore my inputs when I choose something from one of my validated lists.

I was able to modify your code to allow for the D5 selections. If I use the D4 & the D5 selections, one will overwrite the other. Is there a way to make them communicate or verify inputs?

Ex:
I want to choose 480 deflection from D4 and be able to choose 24 spacing from D5 but have it only show one column.

Here is my modified code:

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)

    Dim bottomrow As Long
    bottomrow = Cells(Rows.Count, "B").End(xlUp).Row
    If Target.Cells.Count > 1 Then Exit Sub
    Application.ScreenUpdating = False
    With Me
        If Not Intersect(Target, .Range("D6")) Is Nothing Then
            Select Case Target.Value
                Case "All"
                    .AutoFilterMode = False
                Case Is <> "All"
                    .Range("B12:B" & bottomrow).AutoFilter Field:=1, Criteria1:="=" & Target.Value, visibledropdown:=False
            End Select
        End If
    End With
    
    With Me
        If Not Intersect(Target, .Range("D4")) Is Nothing Then
            Columns("E:J").Hidden = False
            Select Case Target.Value
                Case "360"
                    Columns("H:J").Hidden = True
                Case "480"
                    Columns("E:G").Hidden = True
            End Select
        End If
    End With
    
    With Me
        If Not Intersect(Target, .Range("D5")) Is Nothing Then
            Columns("E:J").Hidden = False
            Select Case Target.Value
                Case "12"
                    Columns("F:G").Hidden = True
                    Columns("I:J").Hidden = True
                Case "16"
                    Columns("E:E").Hidden = True
                    Columns("G:H").Hidden = True
                    Columns("J:J").Hidden = True
                Case "24"
                    Columns("E:F").Hidden = True
                    Columns("H:I").Hidden = True
            End Select
        End If
    End With
             
    Application.ScreenUpdating = True
End Sub

I have also attached my Excel file up to this point.

Thank you in advance for any help!

-Evan

[MarvinP]

Hi emwhite,

I have another idea on how to solve your problem. I've taken your visual table and expanded it into an Excel Table. Excel works well when each row is a separate item and each column has a specific data type. Then you can use Excel Table features like AutoFilter or Advanced Filter to display subsets of your data. This is similar to what you are trying to do with your "Hide Columns".

In the attached go to the Table Tab and use AutoFilter to get to what I think you desire. From this starting point you might want to build dropdowns to help the AutoFilter function. I'm not sure how to correctly label the column heads but I'm sure you'll correct the dimensions.

Using this method you may not even need any VBA code to get the answers you want.

I hope this helps. You were a little stuck with the human readable form of your data but Excel works better if it is expanded like in the Table Tab.

[emwhite]

Hi emwhite,

I have another idea on how to solve your problem. I've taken your visual table and expanded it into an Excel Table. Excel works well when each row is a separate item and each column has a specific data type. Then you can use Excel Table features like AutoFilter or Advanced Filter to display subsets of your data. This is similar to what you are trying to do with your "Hide Columns".

In the attached go to the Table Tab and use AutoFilter to get to what I think you desire. From this starting point you might want to build dropdowns to help the AutoFilter function. I'm not sure how to correctly label the column heads but I'm sure you'll correct the dimensions.

Using this method you may not even need any VBA code to get the answers you want.

I hope this helps. You were a little stuck with the human readable form of your data but Excel works better if it is expanded like in the Table Tab.

I appreciate all of your help and for creating that Table.

All of our span tables for various materials are formatted similar to the Joist tab. I'm afraid it would create confusion for all of our users, so I'm trying to make it is as simplistic as possible but in a format that they are used to seeing/reading.

I may just eliminate the sorting by the deflection (480, 360) to eliminate the confusion there.

[emwhite]

I have now ran into a problem with a "reset" macro. The macro is supposed to reset the data in D3 to blank and the data in D4:D5 to "All".

It works but the hidden tabs are not unhidden with it. I have to manually select "All" for each one in order for it to reset.

Is there a way to fix this? Please see the attached.

TIA

Evan

[MarvinP]

It could be that you have ResetAll as a Private Sub instead of just a Sub.

Remove the word Private and see if it works.

[emwhite]

It could be that you have ResetAll as a Private Sub instead of just a Sub.

Remove the word Private and see if it works.

No luck. Still the same.


I pieced it together form another search from google on another website. So I was afraid that I didn't input something correctly.

Sub ResetAll()

Application.ScreenUpdating = False

Dim myRng As Range

Set myRng = Range("D3:D5")
myRng.Value = "All"
            
Set myRng = Range("D3")
myRng.Value = ""

Application.ScreenUpdating = True

End Sub