Excel宏向结果列添加多余零值单元格问题求助

核心问题分析 #

• 固定范围遍历空行：原代码硬编码取Range("C2:C37")，受试者不足36时会遍历空行，空行的分组值为空，会被归入暴露组分支，且空行年龄默认值为0，导致暴露组混入零值。
• 数组初始化冗余：提前按36个元素初始化数组，未使用的元素默认值为0，若逻辑处理不当会写入表格。

修正方案 #

步骤1：动态获取有效数据行数

不再固定遍历36行，而是找到年龄列（C列）最后一个有数据的行号，只遍历有效数据行。

步骤2：严格判断分组类型

仅当分组明确为"Control"或"Exposed"时才处理，跳过空行或无效分组的行。

步骤3：用集合存储数据

集合自动扩容，只存储有效数据，避免冗余零值元素，写入表格时仅遍历实际数据数量。

修正后的代码 #

Dim lastRow As Long
Dim group As String, age As Variant
Dim i As Long, j As Long
Dim colC As Collection, colE As Collection

' 初始化集合存储两组数据
Set colC = New Collection
Set colE = New Collection

With Sheets("Participant Info")
    ' 获取C列最后一个有数据的行号
    lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
    ' 遍历有效数据行（从第2行到lastRow）
    For r = 2 To lastRow
        group = Trim(.Cells(r, "E").Value) ' 去除首尾空格，避免空格导致判断错误
        age = .Cells(r, "C").Value
        
        ' 仅处理明确的分组
        Select Case group
            Case "Control"
                colC.Add age
            Case "Exposed"
                colE.Add age
            ' 其他情况（空或无效分组）直接跳过
        End Select
    Next r
End With

' 将集合数据写入PRISM-Ready表格
With Sheets("PRISM-Ready")
    ' 清空原有数据（可选，避免残留旧数据）
    .Range("A3:B" & .Rows.Count).ClearContents
    
    ' 写入对照组数据
    For i = 1 To colC.Count
        .Cells(i + 2, 1).Value = colC(i)
    Next i
    
    ' 写入暴露组数据
    For j = 1 To colE.Count
        .Cells(j + 2, 2).Value = colE(j)
    Next j
End With

' 释放集合对象
Set colC = Nothing
Set colE = Nothing

关键优化点 #

• 动态行号：用lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row精准定位有效数据行，避免遍历空行。
• 严谨分组判断：用Trim()处理分组字符串，避免单元格空格导致的判断失误，仅处理指定分组。
• 集合存储优势：自动适配数据量，无冗余元素，写入时仅遍历实际有效数据，彻底解决零值问题。

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