The End If statement in Excel VBA closes an If...Then...Else block, telling the compiler where the conditional logic ends. You must include End If whenever an If statement spans more than one line, and omitting it is one of the most common syntax errors beginners hit.
Why End If Exists
VBA’s If statement has two forms. The single-line form needs no End If:
If Range("A1").Value > 10 Then MsgBox "Big number"
But the block form, which lets you run multiple statements or use ElseIf and Else, requires a closing End If:
If Range("A1").Value > 10 Then
MsgBox "Big number"
Else
MsgBox "Small number"
End If
Think of End If as the closing brace in C-like languages — it marks the boundary of the conditional block. Without it, VBA does not know where the If logic stops, so it throws a compile error: Block If without End If.
How the Block If Structure Works
The full syntax of a block If is:
If condition Then
' statements if true
ElseIf anotherCondition Then
' statements if second condition true
Else
' statements if none true
End If
VBA evaluates the first condition. If it is True, it runs the statements under it and jumps straight to End If, skipping all ElseIf and Else blocks. If the first condition is False, it checks each ElseIf in order. Only if every condition is False does it run the Else block. The ElseIf and Else parts are optional — you can have a simple two-line If...End If with no else at all.
Common Mistakes and Fixes
- Missing End If: Forgetting the closing statement. Fix: always type
End Ifbefore writing the next line of code. - Extra End If: Adding an
End Ifafter a single-lineIf. Single-lineIfdoes not use it. - Mismatched nesting: When you have an
Ifinside anotherIf, each inner block needs its ownEnd If. Indent your code to see the pairing clearly. - Colon misuse: You can put multiple statements on one line using colons, but if you start with
If, you still needEnd Ifunless it is a true single-lineIfwith noElse. For example:If x > 0 Then MsgBox "Pos" Else MsgBox "Neg"is a single line and needs noEnd If.
When to Use Single-Line vs. Block If
Use the single-line form only for very short conditions that fit on one line and need just one statement. For anything with multiple statements, an Else clause, or multiple ElseIf branches, use the block form. Readability is the main reason to prefer block form even for short conditions — it makes the logic easier to debug later. Also, the single-line form cannot contain comments after the statement on the same line, and it is harder to read when the condition is long.
Nested If and ElseIf: A Real Example
Suppose you want to assign a letter grade based on a score in cell A1. A nested or ElseIf structure works well:
Dim score As Integer
score = Range("A1").Value
If score >= 90 Then
Range("B1").Value = "A"
ElseIf score >= 80 Then
Range("B1").Value = "B"
ElseIf score >= 70 Then
Range("B1").Value = "C"
Else
Range("B1").Value = "F"
End If
Notice each ElseIf is on its own line, and there is only one End If at the end. This is cleaner than nesting separate If blocks, though nesting works too:
If score >= 70 Then
If score >= 90 Then
Range("B1").Value = "A"
Else
Range("B1").Value = "C"
End If
Else
Range("B1").Value = "F"
End If
In the nested version, each inner If has its own End If. Always count your End If statements — the number should match the number of block If statements you opened.
Using End If with Select Case and Loops
While End If is only for If blocks, you will often combine it with loops. For example, you might loop through rows and check each cell:
Dim i As Long
For i = 1 To 10
If Cells(i, 1).Value > 100 Then
Cells(i, 2).Value = "High"
End If
Next i
Here, the End If closes the conditional inside the loop. The loop has its own Next i to close it. If you have many conditions, consider Select Case instead — it is often clearer than a long chain of ElseIf and does not use End If; it ends with End Select.
Debugging Missing End If Errors
When you run a macro and get a compile error, the VBA editor highlights the line with the If that is missing its End If. The error message says Block If without End If. To fix it, go to that line, scroll down to where the block should logically end, and add End If. A good habit is to write the End If immediately after typing the If line, then fill in the middle. This prevents the error entirely. Also, always save a copy of your workbook before running any macro, because macros cannot be undone with Ctrl+Z.
Frequently Asked Questions
Can I write If without End If? Yes, but only for a single-line If that has no Else or has a simple Else on the same line. If you press Enter after the Then and write code on the next line, you are in block form and must close with End If.
What is the difference between End If and EndIf? VBA requires a space: End If. Writing EndIf as one word causes a syntax error. The same applies to End Sub, End Function, and End Select.
Do I need End If after every ElseIf? No. You need only one End If at the end of the entire If...ElseIf...Else block. Each ElseIf does not get its own closing statement.
Can I put End If on the same line as the last statement? Yes, you can write End If on the same line as a statement using a colon, for example: If x > 0 Then MsgBox "Pos" : End If. However, this is rarely done because it hurts readability.
Why does my macro say “Block If without End If” when I have an End If? This usually happens when you have an If inside a loop or another If and you forgot one closing End If. Count the number of block If statements and the number of End If lines — they must match exactly.
Is there a keyboard shortcut to auto-insert End If? No built-in shortcut inserts End If automatically, but the VBA editor’s IntelliSense will suggest End If when you type End after a block If. You can also press Tab to accept the suggestion.
Related
- Excel Power Query Undo: What Works and What Doesn’t
- Excel Power Query Ungroup: How to Split Grouped Rows
- Excel Power Query URL: How to Import Data from a Web Page
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA AutoFilter Criteria: Syntax and ExamplesIn Excel VBA, set AutoFilter criteria with the Range.AutoFilter method: specify a filter range that includes its header,…
- Google Sheets Macros: How They Work and What They Can DoYes. Google Sheets has macros that can record repeated actions and replay them later; unlike Excel macros, Sheets…
- VBA Left Function: How to Return Text from the StartIn VBA, use Left(string, length) to return a specified number of characters from the beginning of a string.…
- Excel VBA: Highlight Cells in an Array LoopTo highlight worksheet cells while looping through an array in Excel VBA, read a range into an array,…
- Excel VBA Macro Text: What It Is and How to Use ItAn Excel VBA macro text is the programming code written in Visual Basic for Applications (VBA) that automates…
- VBA Loop Through Worksheets: Examples and TipsTo loop through every worksheet in an Excel workbook, declare a Worksheet variable and use For Each ws…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.