CODEWITHSUNDEEP
excelvba
Jake Holness
Jake Holness

Reputation: 15

Hide non-continuous columns based on the value of another cell (Excel)

I would like to hide columns based on the value (Country Name) of a certain cell.

I am able to hide column based on one country but I am unable to get all three of my countries to work at the same time. Currently my code looks likes this:

Private Sub Worksheet_Selectionchange(ByVal Target As Range)
    If Range("B31").Value = England Then
        Range("E:F,H:I,K:L:N:O").EntireColumn.Hidden = False
    Else
        Range("E:F,H:I,K:L,N:O").EntireColumn.Hidden = True
    End If
        If Range("B31").Value = Wales Then
     Range("D:D,F:G,I:J:L:M,N:O").EntireColumn.Hidden = False
    Else
        Range("D:D,F:G,I:J:L:M,N:O").EntireColumn.Hidden = True
    End If
        If Range("B31").Value = Scotland Then
     Range("D:E,G:H,J:K:M:N").EntireColumn.Hidden = False
    Else
        Range("D:E,G:H,J:K:M:N").EntireColumn.Hidden = True
    End If
End Sub

And when the text is entered in the desired cell all columns are hidden and I would only like the columns not relevant to the country. Is it possible to have only the relevant columns hide when the country is hidden with the current set up/ code I am using. Many thanks, this is my first attempt at macros so I am pretty new.

Upvotes: 1

Views: 608

Answers (2)

VBasic2008
VBasic2008

Reputation: 54807

Show/Hide Non-Contiguous Columns

  • In this case, the Worksheet.Change event will only be triggered when the value in cell B31 is changed manually or via VBA. Manually also includes e.g. a drop-down list.
  • The Worksheet_Change procedure has to be copied to the appropriate sheet module e.g. Sheet1.
  • The other two procedures are usually copied to a standard module e.g. Module1 to easily be called from another worksheet. Only one is to be used. They show the two different approaches:
    1. all columns can be shown and the country-irrelevant columns can be hidden (your way), or
    2. all columns can be hidden and then the country-relevant columns can be shown.
  • UCase and the countries written in UPPERCASE are used to allow case-insensitivity (A = a) in the worksheet cell.

Sheet Module e.g. Sheet1

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
    ' Define monitored cell (range).
    Dim sCell As Range: Set sCell = Range("B31")
    
    ' Check if the value in the monitored cell has changed.
    If Not Intersect(Target, sCell) Is Nothing Then
        Application.ScreenUpdating = False
        ' Either your way...
        HideWay sCell
        ' ... or another way:
        'ShowWay sCell
        Application.ScreenUpdating = True
    End If

End Sub

Standard Module e.g. Module1

Sub HideWay(MonitoredCell As Range)
    With MonitoredCell.Worksheet
        ' Show all columns.
        .Columns("D:O").Hidden = False
        ' Hide country-irrelevant columns.
        Select Case UCase(MonitoredCell.Value)
            Case "ENGLAND"
                ' Showing columns 'D, G, J, M'
                .Range("E:F,H:I,K:L,N:O").EntireColumn.Hidden = True
            Case "WALES"
                ' Showing columns 'E, H, K, N'
                .Range("D:D,F:G,I:J,L:M,O:O").EntireColumn.Hidden = True
            Case "SCOTLAND"
                ' Showing columns 'F, I, L, O'
                .Range("D:E,G:H,J:K,M:N").EntireColumn.Hidden = True
            Case Else
                ' Columns are already shown.
        End Select
    End With
End Sub

Sub ShowWay(MonitoredCell As Range)
    With MonitoredCell.Worksheet
        ' Hide all columns.
        .Columns("D:O").Hidden = True
        ' Show country-relevant columns.
        Select Case UCase(MonitoredCell.Value)
            Case "ENGLAND"
                .Range("D:D,G:G,J:J,M:M").EntireColumn.Hidden = False
            Case "WALES"
                .Range("E:E,H:H,K:K,N:N").EntireColumn.Hidden = False
            Case "SCOTLAND"
                .Range("F:F,I:I,L:L,O:O").EntireColumn.Hidden = False
            Case Else
                ' Show all columns.
                .Columns("D:O").Hidden = False
        End Select
    End With
End Sub

Upvotes: 1

Tomasz
Tomasz

Reputation: 426

if i undersand you right it should help.

Private Sub Worksheet_Selectionchange(ByVal Target As Range)
    dim c as range : set c = Range("B31")
    If c.Value = "England" or c.Value = "Wales" or c.Value = "Scotland" Then
        Range("E:F,H:I,K:L:N:O").EntireColumn.Hidden = False
    Else
        Range("E:F,H:I,K:L,N:O").EntireColumn.Hidden = True
    End 
If End Sub

Upvotes: 0

Related Questions