VBA隐藏行列时出现Block IF without End If报错如何解决

算力阿峰

2026-10-2

错误原因

你的代码出现Block IF without End If报错的核心原因是VBA要求所有块结构必须成对闭合，你的代码缺失了多个结构的闭合语句，同时还有逻辑嵌套错误：

针对C4单元格判断的If Not CheckRange Is Nothing Then分支没有对应End If
遍历C4区域的For Each aCell In CheckRange循环没有对应Next语句
匹配C4地址的第一个Select Case结构没有对应End Select
针对C28/D29/D48判断的If Not CheckRange Is Nothing Then分支没有对应End If
两次设置Application.ScreenUpdating = False后没有改回True，会导致Excel界面刷新异常

修正后代码

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim CheckRange As Range
    Dim aCell As Range
    
    Application.ScreenUpdating = False
    ' 错误捕获，避免异常时屏幕刷新不恢复
    On Error GoTo ErrHandler
    
    ' 处理C4单元格变更逻辑
    Set CheckRange = Intersect(Target, Range("C4"))
    If Not CheckRange Is Nothing Then
        For Each aCell In CheckRange
            Select Case aCell.Address
                Case "$C$4"
                    If aCell.Value = "" Then
                        Sheets("Data input").Range("greenfield").EntireRow.Hidden = True
                        Sheets("Data input").Range("brownfield").EntireRow.Hidden = True
                    ElseIf aCell.Value = "Greenfield" Then
                        Sheets("Data input").Range("greenfield").EntireRow.Hidden = False
                        Sheets("Data input").Range("brownfield").EntireRow.Hidden = True
                    ElseIf aCell.Value = "Brownfield" Then
                        Sheets("Data input").Range("greenfield").EntireRow.Hidden = True
                        Sheets("Data input").Range("brownfield").EntireRow.Hidden = False
                    End If
            End Select
        Next aCell
    End If
    
    ' 处理C28/D29/D48单元格变更逻辑
    Set CheckRange = Intersect(Target, Range("C28,D29,D48"))
    If Not CheckRange Is Nothing Then
        For Each aCell In CheckRange
            Select Case aCell.Address
                Case "$C$28"
                    If aCell.Value = "" Then
                        Sheets("Data input").Range("press1table").EntireRow.Hidden = True
                        Sheets("Data input").Range("press1stages").EntireRow.Hidden = True
                        Sheets("Data input").Range("press1input2").ClearContents
                        Sheets("Data input").Range("press1input10").ClearContents
                        Sheets("Data input").Range("press1input11").ClearContents
                        Sheets("Data input").Range("press1input12").ClearContents
                        Sheets("Data input").Range("press1input13").ClearContents
                        Sheets("Data input").Range("press1input14").ClearContents
                    ElseIf aCell.Value = "Manual ops" Or aCell.Value = "Manual ops (ganged)" Then
                        Sheets("Data input").Range("press1table").EntireRow.Hidden = True
                        Sheets("Data input").Range("processcount1").EntireRow.Hidden = False
                        Sheets("Data input").Range("press1stages").EntireRow.Hidden = True
                        Sheets("Data input").Range("press1input2").ClearContents
                        Sheets("Data input").Range("press1input10").ClearContents
                        Sheets("Data input").Range("press1input11").ClearContents
                        Sheets("Data input").Range("press1input12").ClearContents
                        Sheets("Data input").Range("press1input13").ClearContents
                        Sheets("Data input").Range("press1input14").ClearContents
                    ElseIf aCell.Value = "Progression press" Then
                        Sheets("Data input").Range("processcount1").EntireRow.Hidden = True
                        Sheets("Data input").Range("press1stages").EntireRow.Hidden = False
                        Sheets("Data input").Range("pressproctable1").EntireRow.Hidden = False
                        Sheets("Data input").Range("prog1").EntireRow.Hidden = False
                        Sheets("Data input").Range("trans1").EntireRow.Hidden = True
                        Sheets("Data input").Range("tand1").EntireRow.Hidden = True
                        Sheets("Data input").Range("manproc15").EntireRow.Hidden = True
                        Sheets("Data input").Range("secondops1").EntireRow.Hidden = True
                        Sheets("Data input").Range("press1input2").ClearContents
                        Sheets("Data input").Range("press1input10").ClearContents
                        Sheets("Data input").Range("press1input11").ClearContents
                        Sheets("Data input").Range("press1input12").ClearContents
                        Sheets("Data input").Range("press1input13").ClearContents
                        Sheets("Data input").Range("press1input14").ClearContents
                    End If
                ' 如需处理D29、D48的变更逻辑，可在这里补充Case分支
                ' Case "$D$29"
                ' 对应逻辑
                ' Case "$D$48"
                ' 对应逻辑
            End Select
        Next aCell
    End If

ErrHandler:
    Application.ScreenUpdating = True
End Sub

优化说明

合并了重复的Application.ScreenUpdating设置，新增错误捕获保证异常时也能恢复屏幕刷新
把重复的If判断改成了ElseIf分支，逻辑更清晰
相同执行逻辑的"Manual ops"和"Manual ops (ganged)"分支做了合并，减少冗余代码
补充了D29、D48分支的预留位置，可根据需求自行补充对应逻辑

内容的提问来源于stack exchange，提问作者samjones833