How to hide/unhide columns based on a specific value VBA

Question

I am trying to create a script that hides or unhides specific Columns when a value is entered into a Column using VBA.

For instance, if X is entered into any cell in Column A then hide Columns B:C. But if the value in Column A is Y then Columns D:E but show Columns B:C.

This is what I've tried thus far:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Range("A").Value = "X" Then
        Columns("B:C").EntireColumn.Hidden = True
    ElseIf Range("A").Value = "Y" Then
        Columns("D:E").EntireColumn.Hidden = True
    Else
    End If
End Sub

score 1 · Accepted Answer · 2018-10-10 01:27:46Z

1

You need Worksheet_Change, not Worksheet_SelectionChange.

There is also some logic conflict. What if column A contains both an X and a Y? This routine makes the 'hide columns' decision on the last value placed in column A.

Private Sub Worksheet_Change(ByVal Target As Range)

    if not intersect(target, range("a:a")) is nothing then
        on error goto safe_exit
        application.enableevents = false
        dim t as range 
        for each t in intersect(target, range("a:a"))
            select case ucase(t.value)
                case "X"
                    Columns("B:C").EntireColumn.Hidden = True
                    Columns("D:E").EntireColumn.Hidden = false
                case "Y"
                    Columns("B:C").EntireColumn.Hidden = false
                    Columns("D:E").EntireColumn.Hidden = true
                case else
                    'do nothing
            end select
        next t
    End If

safe_exit:
    application.enableevents = true
End Sub

Get rid of the Worksheet_SelectionChange event procedure you've created after adding this to the worksheet's private code sheet.

edited Oct 10, 2018 at 1:27

answered Oct 10, 2018 at 1:22

user4039065

Sign up to request clarification or add additional context in comments.

3 Comments

user4039065 Over a year ago

Please note the logic conflict I mentioned in an edit to above.

Chopin Over a year ago

Yeh I noticed that when I was testing it out. The actual script will contain numerous values that will hide/unhide colums. The currently displayed columns will have to be the associated with the last placed value. I do have a query though. This script is for a master database. I'm hoping to automatically copy specific values and columns into individual sheets. So all the X's into a separate sheet etc. will this be affected by the hide/unhide logic?

user4039065 Over a year ago

If you paste (or otherwise change the values) in a number of cells in column A, each will be processed. Since they receive the same processing, the last one processed will be the final result. This isn't every value in column A, only the ones that receive new values.

Collectives™ on Stack Overflow

How to hide/unhide columns based on a specific value VBA

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related