CODEWITHSUNDEEP
excelselectmultiple-columnsvba
AsZ
AsZ

Reputation: 11

Selecting multiple columns in Excel

I am currently working on a project at my work which requires good understanding of Excel. For this project I have to design an Excel template and I am currently working on this.

I have some knowledge of how to use Excel, but that does not contain coding in Excel by using VBA.

I have found some code on the internet which is not fully helping me with my issue. My issue is that I want to hide some columns based on the outcome of a function in one cell. As you can see below, I already created some code, but after trying everything I still could not manage to hide multiple columns. I have found some solutions on the internet to select multiple columns, but unfortunately, I could not manage to use it in my code. Please let me know if you have any solutions for my issue. Thanks in advance for your help. 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Range("B5").Value = "USD" Then
        Columns("C").EntireColumn.Hidden = True
    ElseIf Range("B5").Value = "LC" Then
        Columns("C").EntireColumn.Hidden = False
    End If
End Sub

Upvotes: 1

Views: 169

Answers (2)

AsZ
AsZ

Reputation: 11

Please find below the code that helped me with my issue. Thanks to the users Pᴇʜ and sous2817. Hope that this may help someone else. 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Range("B5").Value = "USD" Then
        Union(Columns("C"), Columns("E")).EntireColumn.Hidden = True
    ElseIf Range("B5").Value = "LC" Then
        Union(Columns("C"), Columns("E")).EntireColumn.Hidden = False
    End If
End Sub

Upvotes: 0

sous2817
sous2817

Reputation: 3960

There are probably several ways to solve your issue. When interacting with more than one row or column, you need to use the Range property. Here is one way (assuming a contiguous range):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Range("B5").Value = "USD" Then
        Range("C:D").EntireColumn.Hidden = True
    ElseIf Range("B5").Value = "LC" Then
        Range("C:D").EntireColumn.Hidden = False
    End If
End Sub

Also, as Pᴇʜ points out in the comments, it might be better to use the Change event as you only really care if / when B5 changes. The SelectionChange event probably fires more than you need it to (again, pending on your sheet setup). Here is the same code using a different event:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$5" Then
        If Target.Value = "USD" Then
            Range("C:D").EntireColumn.Hidden = True
        ElseIf Target.Value = "LC" Then
            Range("C:D").EntireColumn.Hidden = False
        End If
    End If       
End Sub

Just to complete the overall ask, here is a way to go about the same task with non-contiguous range(s):

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$5" Then
        If Target.Value = "USD" Then
            Union(Columns("C"), Columns("G")).EntireColumn.Hidden = True
        ElseIf Target.Value = "LC" Then
            Union(Columns("C"), Columns("G")).EntireColumn.Hidden = False
        End If
    End If
End Sub

Upvotes: 1

Related Questions