Excel VBA to hide and unhide columns based on a dropdown validation list selection

Question

I have a dropdown validation list in cell A1 with category items like "All", "Online store", "Department store", "Specialized store" and so on. Then, from cell B1 to X1 I have the before mentioned categories except "All".

I want to hide all columns except the ones from the category selected in the dropdown validation list. Also I need to unhide all columns if I select "All" in the list.

I found a sample code on the Internet which works fine to hide the non selected categories -but quite slow response when changing selection-. But I could not make it works together with a code to unhide all columns.

The related code is below. Thanks for your feedback.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim R, V

If Target.Address = ("$A$1") Then
V = [A1].Value
For Each R In Range("B1:X1")
R.EntireColumn.Hidden = R.Value <> V
Next

End If

End Sub

what do you mean quite slow response when changing selection? Just changing selection shouldn't matter since this is a Change event, not a SelectionChange event — ashleedawg
If I select any of the category in the list, I have a lag of 5 seconds or more before to get a display of the new selection of columns. — D.Fox

paul bica paul bica · Accepted Answer · 2018-04-07T22:29:28

To make your code faster turn off ScreenUpdating before looping and back on after

To add the "All" functionality use the code bellow

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range) 'Target = cell being mdified (changed)

    Dim c As Variant, v As String

    If Target.Address = "$A$1" Then 'If edited cell is A1

        v = Target.Value2           '.Value2 = the text in the cell (without formatting)

        With Range("B1:X1")

            Application.ScreenUpdating = False

            .EntireColumn.Hidden = (v <> "All") 'Hides / Unhides all

            If v <> "All" Then  'If all are hidden, unhide the ones for criteria
                For Each c In .Cells
                    If c = v Then c.EntireColumn.Hidden = False
                Next
            End If

            Application.ScreenUpdating = True
        End With
    End If
End Sub

More details about .Value2

Excel VBA to hide and unhide columns based on a dropdown validation list selection

1 Answers