Excel VBA用户窗体按钮：完善行计数功能（含隐藏行统计）

问题分析 #

现有代码仅统计了可见行中五类状态的数量，未对隐藏行的状态做分类统计，也未计算以下关键指标：

• 包含隐藏行的总行数
• 隐藏行总数
• 不属于五类的行数量（分可见/隐藏）

修改后的VBA代码 #

Private Sub CommandButton5_Click()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Default") ' 明确指定工作表，避免ActiveSheet歧义
    
    ' 格式化输出列
    With ws.Columns("P:X")
        .ColumnWidth = 27.5
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With
    
    ' 统计变量定义：分可见(Vis)和隐藏(Hid)两类
    Dim totalRows As Long ' 总行数（含隐藏）
    Dim visTotal As Long, hidTotal As Long ' 可见/隐藏行总数
    ' 五类状态的可见/隐藏计数
    Dim visIncorporated As Long, hidIncorporated As Long
    Dim visToBe As Long, hidToBe As Long
    Dim visWontBe As Long, hidWontBe As Long
    Dim visPartial As Long, hidPartial As Long
    Dim visReplaced As Long, hidReplaced As Long
    ' 非五类状态的可见/隐藏计数
    Dim visNonStatus As Long, hidNonStatus As Long
    
    ' 获取数据总行数（从A列最后一行判断）
    totalRows = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' 遍历数据行（从第2行开始，假设第1行是表头）
    Dim rowNum As Long
    For rowNum = 2 To totalRows
        Dim isHidden As Boolean
        isHidden = ws.Rows(rowNum).Hidden
        Dim statusVal As String
        statusVal = Trim(ws.Cells(rowNum, "J").Value) ' 去除空格避免匹配错误
        
        Select Case statusVal
            Case "Incorporated"
                If isHidden Then hidIncorporated = hidIncorporated + 1 Else visIncorporated = visIncorporated + 1
            Case "To be incorporated"
                If isHidden Then hidToBe = hidToBe + 1 Else visToBe = visToBe + 1
            Case "Won't be incorporated"
                If isHidden Then hidWontBe = hidWontBe + 1 Else visWontBe = visWontBe + 1
            Case "Partially incorporated"
                If isHidden Then hidPartial = hidPartial + 1 Else visPartial = visPartial + 1
            Case "Replaced by other EA"
                If isHidden Then hidReplaced = hidReplaced + 1 Else visReplaced = visReplaced + 1
            Case Else
                ' 不属于五类的行
                If isHidden Then hidNonStatus = hidNonStatus + 1 Else visNonStatus = visNonStatus + 1
        End Select
    Next rowNum
    
    ' 计算汇总值
    visTotal = visIncorporated + visToBe + visWontBe + visPartial + visReplaced + visNonStatus
    hidTotal = totalRows - 1 - visTotal ' 减1是因为排除表头行
    Dim totalNonStatus As Long: totalNonStatus = visNonStatus + hidNonStatus
    Dim totalWithStatus As Long: totalWithStatus = totalRows - 1 - totalNonStatus ' 五类状态总行数
    
    ' 输出统计结果到表格（从总行数下一行开始，避免覆盖数据）
    Dim outputRow As Long
    outputRow = totalRows + 2 ' 空一行分隔数据和统计结果
    
    ' 输出表头
    ws.Cells(outputRow, "P").Value = "统计项"
    ws.Cells(outputRow, "Q").Value = "可见行数量"
    ws.Cells(outputRow, "R").Value = "隐藏行数量"
    ws.Cells(outputRow, "S").Value = "总计（含隐藏）"
    
    ' 输出各类统计数据
    outputRow = outputRow + 1
    ws.Cells(outputRow, "P").Value = "EAs that are incorporated"
    ws.Cells(outputRow, "Q").Value = visIncorporated
    ws.Cells(outputRow, "R").Value = hidIncorporated
    ws.Cells(outputRow, "S").Value = visIncorporated + hidIncorporated
    
    outputRow = outputRow + 1
    ws.Cells(outputRow, "P").Value = "EAs that are To be Incorporated"
    ws.Cells(outputRow, "Q").Value = visToBe
    ws.Cells(outputRow, "R").Value = hidToBe
    ws.Cells(outputRow, "S").Value = visToBe + hidToBe
    
    outputRow = outputRow + 1
    ws.Cells(outputRow, "P").Value = "EAs that won't be Incorporated"
    ws.Cells(outputRow, "Q").Value = visWontBe
    ws.Cells(outputRow, "R").Value = hidWontBe
    ws.Cells(outputRow, "S").Value = visWontBe + hidWontBe
    
    outputRow = outputRow + 1
    ws.Cells(outputRow, "P").Value = "EAs with partially incorporated"
    ws.Cells(outputRow, "Q").Value = visPartial
    ws.Cells(outputRow, "R").Value = hidPartial
    ws.Cells(outputRow, "S").Value = visPartial + hidPartial
    
    outputRow = outputRow + 1
    ws.Cells(outputRow, "P").Value = "EAs Replaced by other EA"
    ws.Cells(outputRow, "Q").Value = visReplaced
    ws.Cells(outputRow, "R").Value = hidReplaced
    ws.Cells(outputRow, "S").Value = visReplaced + hidReplaced
    
    outputRow = outputRow + 1
    ws.Cells(outputRow, "P").Value = "EAs without incorporation status"
    ws.Cells(outputRow, "Q").Value = visNonStatus
    ws.Cells(outputRow, "R").Value = hidNonStatus
    ws.Cells(outputRow, "S").Value = totalNonStatus
    
    outputRow = outputRow + 1
    ws.Cells(outputRow, "P").Value = "总行数（含隐藏）"
    ws.Cells(outputRow, "S").Value = totalRows - 1 ' 排除表头
    
    outputRow = outputRow + 1
    ws.Cells(outputRow, "P").Value = "可见行总数"
    ws.Cells(outputRow, "S").Value = visTotal
    
    outputRow = outputRow + 1
    ws.Cells(outputRow, "P").Value = "隐藏行总数"
    ws.Cells(outputRow, "S").Value = hidTotal
    
    ' 加粗表头
    ws.Range(ws.Cells(totalRows + 2, "P"), ws.Cells(totalRows + 2, "S")).Font.Bold = True
    
    MsgBox "统计完成！结果已输出到数据下方。"
End Sub

关键改进说明 #

1. 变量分组管理：将统计变量分为可见/隐藏两组，清晰区分不同状态的行数据。
2. 完整遍历所有行：遍历所有数据行（包括隐藏行），通过Rows(rowNum).Hidden判断行状态，再根据J列值分类计数。
3. 计算缺失指标：
   • 总行数：直接从A列最后一行获取，减去表头行得到有效数据总行数。
   • 隐藏行总数：总行数减去可见行总数（可见行总数由各类可见统计值相加得到）。
   • 非五类行数量：遍历中捕获所有不匹配五类状态的行，分可见/隐藏统计，再汇总。
4. 优化输出格式：将统计结果输出到数据下方的新区域，避免覆盖原始数据，同时增加总计列，让结果更直观。
5. 避免ActiveSheet歧义：明确指定操作的工作表为Worksheets("Default")，防止切换工作表时出错。

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