0

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
Community
  • 1
  • 1
D.Fox
  • 43
  • 1
  • 7
  • 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 Apr 07 '18 at 13:16
  • 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 Apr 07 '18 at 23:43

1 Answers1

0

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

paul bica
  • 10,557
  • 4
  • 23
  • 42
  • The first part of the code works perfectly to unhide all columns. About the second part, I still have an issue as it shows only one column at a time -the first on the left that meets the criteria- and I need all columns meeting the same criteria to be displayed. Note that I have no lag anymore since it is not updating screen. Last, would you please explain me the signification of Target.Value2? Here, I wonder about why you added the number "2". – D.Fox Apr 08 '18 at 00:04
  • I made the changes, and added more details about `Target` and `.Value2` – paul bica Apr 08 '18 at 05:04
  • Perfect! It works fine, thank you. And I will keep that in mind about the syntax .Value2. – D.Fox Apr 08 '18 at 09:21