VBA Select Case in Excel — 6 Examples (Ranges, Is, Multiple Values)

Tested on: Excel 365 v2509 · Excel 2021 · Excel 2019 · last verified 2026-06-05

TL;DR — Select Case tests one variable against many possible values — cleaner than a stack of ElseIfs. Copy this and adapt:

Sub GradeScore()
    Dim score As Long
    score = Range("A1").Value

    Select Case score
        Case Is >= 90:  Range("B1").Value = "A"
        Case 75 To 89:  Range("B1").Value = "B"
        Case 60 To 74:  Range("B1").Value = "C"
        Case Else:      Range("B1").Value = "Fail"
    End Select
End Sub

Notice how readable that is versus four ElseIf lines. The rest of this guide covers every matching style: exact values, lists, ranges, comparisons, and the Select Case True power move.

Why Select Case beats a wall of ElseIf

When you test the same variable against several values, ElseIf gets noisy fast. Select Case says the variable name once, lines up the options, and stops at the first match — easier to read, easier to extend, and slightly faster.

How it works

VBA evaluates the test expression once, then checks each Case top to bottom. The first match wins — remaining cases are skipped. Case Else catches everything not matched above (always put it last).

Example 1 — Exact values and comma-separated lists

A single Case can hold several values separated by commas:

Sub ClassifyCountry()
    Dim country As String
    country = Range("A2").Value

    Select Case country
        Case "France", "Germany", "Italy", "Spain"
            Range("B2").Value = "Europe"
        Case "Japan", "Korea", "China"
            Range("B2").Value = "Asia"
        Case Else
            Range("B2").Value = "Other"
    End Select
End Sub

Example 2 — Numeric ranges with To

To matches an inclusive range — perfect for tiers and bands:

Select Case orderTotal
    Case 0 To 99.99:      discount = 0
    Case 100 To 499.99:   discount = 0.05
    Case 500 To 999.99:   discount = 0.1
    Case Else:            discount = 0.15
End Select

Example 3 — Is with comparison operators

When a range won't express it, Is lets you use >, <, >=, <=, <>:

Select Case temperature
    Case Is < 0:    status = "Freezing"
    Case Is <= 15:  status = "Cold"
    Case Is <= 25:  status = "Mild"
    Case Else:      status = "Hot"
End Select

Order matters here: because the first match wins, list your cases from the most restrictive boundary upward.

Example 4 — Mix lists, ranges and Is in one block

Select Case score
    Case 0, 1, 2:        rating = "Poor"          ' a list
    Case 3 To 6:         rating = "Average"        ' a range
    Case Is >= 7:        rating = "Good"           ' a comparison
    Case Else:           rating = "Unrated"
End Select

All three matching styles coexist freely. That flexibility is why Select Case scales where ElseIf chains become unreadable.

Example 5 — The Select Case True trick

Need to test conditions on different variables, or complex logic, but still want the clean Case layout? Switch the test expression to True:

Select Case True
    Case age < 18 And hasGuardian:    fare = "Child"
    Case age >= 65:                   fare = "Senior"
    Case isStudent And age < 26:      fare = "Student"
    Case Else:                        fare = "Adult"
End Select

Each Case is now a full Boolean expression. This is the idiomatic VBA way to keep multi-variable logic readable — the one place Select Case reaches past a single variable.

Example 6 — Case Else as a safety net

Always include Case Else, even just to catch surprises:

Select Case Weekday(Date)
    Case vbMonday To vbFriday:  MsgBox "Workday"
    Case vbSaturday, vbSunday:  MsgBox "Weekend"
    Case Else:                  MsgBox "Unexpected date value"   ' should never hit
End Select

A Case Else that "should never run" is how you catch the data you didn't anticipate instead of silently doing nothing.

Select Case vs If/ElseIf — which to use

Rule of thumb: same variable, many outcomes → Select Case. Different variables, few outcomes → If.

Common errors (and the fix)

Two cases overlap and the wrong one fires — Remember the first match wins. Case Is < 100 before Case Is < 50 means values under 50 never reach the second case. Order from most specific to least.

Case x > 5 does nothing — You can't write a bare comparison; use Case Is > 5. The Is keyword is required when comparing.

Text match fails on case — Select Case is case-sensitive by default. Add Option Compare Text at the top of the module, or normalize with Select Case LCase(country).

Stop maintaining branching logic by hand

Select Case is cleaner than ElseIf, but it's still code you have to test, debug, and update every time the business rules change. ExcelMaster Agent turns the rule itself into the interface — "band column A: under 100 = no discount, 100–499 = 5%, 500+ = 10%" — and generates the logic for you, ranges and edge cases included. Try it free →

Related guides

• VBA If Then Else — 6 Real-World Examples
• VBA For Loop in Excel — 8 Real-World Examples
• VBA While Loop — While, Do While & Do Until Explained

FAQ

Can VBA Select Case test a range of values? Yes — Case 1 To 10 matches any value from 1 to 10 inclusive. You can combine ranges, lists, and Is comparisons in the same block.

How do I test multiple values in one case? Separate them with commas: Case "A", "B", "C" matches any of the three. Ranges and comparisons can join the list too: Case 1, 2, 5 To 9.

When should I use Select Case instead of If? When you're testing the same variable against three or more values. For one or two outcomes, or conditions spanning different variables, If...ElseIf is fine.

Is VBA Select Case faster than ElseIf? Marginally, because the test expression is evaluated once. The bigger win is readability and maintainability, not raw speed.