如何让Excel VBA宏根据数据行数自动填充T2、U2公式？

核心修改点 #

• 替换原代码中固定填充到T70、U70的静态设置，改为根据工作表实际数据行数自动匹配填充范围
• 移除冗余的Select操作，提升宏的运行效率和稳定性

优化后的完整代码 #

Sub PenaltyNew()
'
' PenaltyNew Macro
' Keyboard Shortcut: Ctrl+Shift+P
'
    Dim lastRow As Long
    ' 获取当前工作表有数据的最后一行（以A列为基准，可根据实际数据列调整）
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    ' 写入T2单元格公式并自动填充到最后一行
    Range("T2").Formula2R1C1 = _
        "=LOOKUP(2,1/(R2C[-19]:RC[-19]<>""""),ROW(R2C[-19]:RC[-19]))"
    Range("T2:T" & lastRow).FillDown
    
    ' 写入U2单元格公式并自动填充到最后一行
    Range("U2").Formula2R1C1 = _
        "=AND(ISNUMBER(SEARCH(""MISSED BREAK"",UPPER(TRIM(INDEX(C3,RC20))))),OR(UPPER(TRIM(RC18))=""MISSED"",RC18=0))"
    Range("U2:U" & lastRow).FillDown
    
    ' 重命名工作表并生成新表（保留原逻辑）
    Sheets("TimeEntries_2026_04_15-2026_04_").Name = "Sheet1"
    Sheets.Add After:=ActiveSheet
    ActiveCell.Formula2R1C1 = _
        "=VSTACK(Sheet1!RC:RC[18],LET(hdr,Sheet1!R[1]C[19]:R[999]C[19],keep,Sheet1!R[1]C[20]:R[999]C[20],rowsAbs,FILTER(ROW(hdr),keep),hdrAbs,INDEX(hdr,rowsAbs-ROW(Sheet1!R2C[19])+1),hdrRel,hdrAbs-ROW(Sheet1!R2C)+1,rowsRel,rowsAbs-ROW(Sheet1!R2C[14])+1,CHOOSE({1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19}, INDEX(Sheet1!R2C:R1000C,hdrRel), INDEX(Sheet1!R2C[1]:R1000C[1],hdr" & _
        "Rel), INDEX(Sheet1!R2C[2]:R1000C[2],hdrRel), INDEX(Sheet1!R2C[3]:R1000C[3],hdrRel), INDEX(Sheet1!R2C[4]:R1000C[4],hdrRel), IFERROR(VALUE(INDEX(Sheet1!R2C[5]:R1000C[5],hdrRel)),INDEX(Sheet1!R2C[5]:R1000C[5],hdrRel))," & Chr(10) & "IFERROR(VALUE(INDEX(Sheet1!R2C[6]:R1000C[6],hdrRel)),INDEX(Sheet1!R2C[6]:R1000C[6],hdrRel)), IFERROR(VALUE(INDEX(Sheet1!R2C[7]:R1000C[7],hdrRel)),INDEX(" & _
        "Sheet1!R2C[7]:R1000C[7],hdrRel)), INDEX(Sheet1!R2C[8]:R1000C[8],hdrRel), INDEX(Sheet1!R2C[9]:R1000C[9],hdrRel), INDEX(Sheet1!R2C[10]:R1000C[10],hdrRel), INDEX(Sheet1!R2C[11]:R1000C[11],hdrRel), INDEX(Sheet1!R2C[12]:R1000C[12],hdrRel), INDEX(Sheet1!R2C[13]:R1000C[13],hdrRel), INDEX(Sheet1!R2C[14]:R1000C[14],rowsRel), IF(LEN(TRIM(INDEX(Sheet1!R2C[15]:R1000C[15],rowsRe" & _
        "l)))=0,"""",IFERROR(VALUE(INDEX(Sheet1!R2C[15]:R1000C[15],rowsRel)),INDEX(Sheet1!R2C[15]:R1000C[15],rowsRel))), IF(LEN(TRIM(INDEX(Sheet1!R2C[16]:R1000C[16],rowsRel)))=0,"""",IFERROR(VALUE(INDEX(Sheet1!R2C[16]:R1000C[16],rowsRel)),INDEX(Sheet1!R2C[16]:R1000C[16],rowsRel))), INDEX(Sheet1!R2C[17]:R1000C[17],rowsRel), INDEX(Sheet1!R2C[18]:R1000C[18],rowsRel))))"
    Range("A2").Select
End Sub

关键修改说明 #

1. 获取实际数据行数：通过Cells(Rows.Count, "A").End(xlUp).Row获取A列最后一个有数据的行号，如果你数据的主列不是A列，把"A"改成对应列标即可（比如"C"）。
2. 替换固定填充范围：用Range("T2:T" & lastRow)和Range("U2:U" & lastRow)替代原固定的T2:T70、U2:U70，实现自动适配。
3. 简化填充操作：用FillDown替代AutoFill，代码更简洁，效果一致。
4. 移除冗余Select：直接对目标单元格写入公式，不需要先选中单元格，避免因选中状态异常导致的报错。

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