Excel VBA宏执行异常排查：添加统计步骤后功能失效

问题概述 #

作为VBA新手，录制了Excel宏用于数据筛选和排序，需求是在现有宏中添加统计步骤：对筛选后的数据，在新工作表中统计L列的总数值数量，以及其中≤45的数值数量。使用AI生成的补充代码执行时无报错，但出现意外表格设计变更，且统计功能失效。

故障排查 #

AI生成的代码存在以下核心问题：

• 表头行定位错误：原数据的表头位于第2行（原录制宏以A2:L2作为筛选表头），但AI代码错误地将第3行设为筛选起点，导致筛选范围错位，破坏了原有表格结构，同时筛选逻辑失效。
• 统计未区分可见行：循环统计时遍历了所有行，包括筛选后隐藏的非目标数据，导致统计结果包含无效数据，不符合需求。
• 变量未声明：循环变量i未声明，不符合VBA规范，可能引发潜在错误。
• 未处理工作表重名：若工作簿中已存在名为Counts的工作表，执行时会直接报错，缺乏容错处理。

修正后的完整宏代码 #

Option Explicit

Sub Macro6()
    Dim wsData As Worksheet
    Dim wsCounts As Worksheet
    Dim lastRow As Long
    Dim totalCount As Long
    Dim countLessEqualTo45 As Long
    Dim cell As Range
    
    ' 绑定数据工作表（假设数据在Sheet1）
    Set wsData = ThisWorkbook.Worksheets("Sheet1")
    
    ' 清除原有筛选
    If wsData.AutoFilterMode Then wsData.AutoFilterMode = False
    
    ' 执行筛选：表头在第2行，数据范围从A2到L列最后一行
    lastRow = wsData.Cells(wsData.Rows.Count, "L").End(xlUp).Row
    wsData.Range("A2:L" & lastRow).AutoFilter Field:=1, Criteria1:="NW EMER"
    wsData.Range("A2:L" & lastRow).AutoFilter Field:=7, Criteria1:="CBC7"
    
    ' 对筛选后的数据按L列升序排序
    With wsData.AutoFilter.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=wsData.Range("L2:L" & lastRow), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    ' 统计筛选后可见行的L列数据
    totalCount = 0
    countLessEqualTo45 = 0
    ' 遍历L列可见单元格（从第3行开始，跳过表头）
    For Each cell In wsData.Range("L3:L" & lastRow).SpecialCells(xlCellTypeVisible)
        totalCount = totalCount + 1
        If IsNumeric(cell.Value) And cell.Value <= 45 Then
            countLessEqualTo45 = countLessEqualTo45 + 1
        End If
    Next cell
    
    ' 创建或复用统计工作表
    On Error Resume Next
    Set wsCounts = ThisWorkbook.Worksheets("Counts")
    On Error GoTo 0
    If wsCounts Is Nothing Then
        Set wsCounts = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        wsCounts.Name = "Counts"
    Else
        ' 清空原有统计内容
        wsCounts.Range("A1:B2").ClearContents
    End If
    
    ' 写入统计结果
    wsCounts.Range("A1").Value = "L列≤45的数值数量"
    wsCounts.Range("A2").Value = countLessEqualTo45
    wsCounts.Range("B1").Value = "L列总数值数量"
    wsCounts.Range("B2").Value = totalCount
    
    ' 可选：自动调整列宽
    wsCounts.Columns("A:B").AutoFit
End Sub

代码关键改进说明 #

1. 精准定位数据范围：通过lastRow动态获取L列最后一行，避免硬编码行号，适配数据量变化。
2. 仅统计可见行：使用SpecialCells(xlCellTypeVisible)只遍历筛选后的可见行，确保统计结果准确。
3. 容错处理工作表重名：先尝试获取已存在的Counts工作表，不存在再新建，避免重复创建或报错。
4. 变量声明与规范：添加Option Explicit强制变量声明，绑定工作表对象避免ActiveSheet引发的上下文错误。
5. 数值有效性判断：增加IsNumeric判断，避免非数值单元格干扰统计结果。

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