VBA Excel multiple elseif statement

Hide Rows (Match / Select Case)

  • The Select Case version is case-sensitive while the Application.Match version is not.

The Code

Option Explicit

Sub geographyMatch()
    
    Const RowNumbers As String = "3:165"
    Dim Criteria As Variant
    Criteria = Array("GIS", "CLIMATE", "TRAVEL", "TOURISM", "WILDLIFE")
    
    Worksheets("Social").Rows(RowNumbers).EntireRow.Hidden = True
    
    Dim rng As Range
    Dim cel As Range
    For Each cel In Worksheets("Social").Columns("F").Rows(RowNumbers)
        If Not IsError(Application.Match(cel.Value, Criteria, 0)) Then
            If Not rng Is Nothing Then
                Set rng = Union(rng, cel)
            Else
                Set rng = cel
            End If
        End If
    Next cel
    
    If Not rng Is Nothing Then
        rng.EntireRow.Hidden = False
    End If

End Sub

Sub geographySelectCase()
    
    Const RowNumbers As String = "3:165"
    
    Worksheets("Social").Rows(RowNumbers).EntireRow.Hidden = True
    
    Dim rng As Range
    Dim cel As Range
    For Each cel In Worksheets("Social").Columns("F").Rows(RowNumbers)
        Select Case cel.Value
            Case "GIS", "CLIMATE", "TRAVEL", "TOURISM", "WILDLIFE"
                If Not rng Is Nothing Then
                    Set rng = Union(rng, cel)
                Else
                    Set rng = cel
                End If
        End Select
    Next cel
    
    If Not rng Is Nothing Then
        rng.EntireRow.Hidden = False
    End If

End Sub

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top