如何用Excel VBA自动适配新增列的Concatenate+IF公式？

问题背景 #

我正尝试通过编码在Excel VBA中自动化以下公式：

Dim ws As Worksheet
Set ws = Sheet2

Dim frow As Long
frow = Sheet2.UsedRange.Rows.Count

ws.Range("W2:W" & frow).FormulaR1C1 = "CONCATENATE(IF(ISBLANK(W2), """",W2&CHAR(10)),IF(ISBLANK(X2),"""",X2&CHAR(10)),IF(ISBLANK(Y2), """",Y2&CHAR(10)),IF(ISBLANK(Z2),"""",Z2&CHAR(10)))"

注：修正了原代码的语法问题（变量声明缺失As、中文引号替换为英文引号）。

该公式目前可正常运行，但后续会新增列，无法手动修改公式适配所有新增列，需要更灵活的自动化方式。

方案1：使用TEXTJOIN函数（推荐，Excel 2019+） #

TEXTJOIN原生支持忽略空白单元格，只需指定目标范围即可自动拼接，完全适配列新增场景。代码如下：

Sub AutoConcatenateWithTEXTJOIN()
    Dim ws As Worksheet
    Set ws = Sheet2
    
    Dim lastRow As Long
    lastRow = ws.UsedRange.Rows.Count
    
    ' 定义拼接范围的起始列和最后一列
    Dim startCol As Long, endCol As Long
    startCol = ws.Range("W1").Column ' 起始列为W列，可按需调整
    endCol = ws.UsedRange.Columns(ws.UsedRange.Columns.Count).Column ' 自动获取最后一列
    
    ' 生成R1C1格式的TEXTJOIN公式
    Dim formulaStr As String
    formulaStr = "=TEXTJOIN(CHAR(10), TRUE, RC[" & startCol - ws.Range("W1").Column & "]:RC[" & endCol - ws.Range("W1").Column & "])"
    
    ' 应用公式并设置自动换行
    ws.Range("W2:W" & lastRow).FormulaR1C1 = formulaStr
    ws.Range("W2:W" & lastRow).WrapText = True
End Sub

核心优势：

• 自动识别新增列，无需修改代码
• TRUE参数自动跳过空白单元格，省去冗余的IF判断
• R1C1引用样式确保公式在不同行/列下的正确性

方案2：兼容旧版Excel（无TEXTJOIN时） #

如果需要支持Excel 2016及更早版本，可通过VBA动态生成CONCATENATE+IF公式，自动遍历所有目标列：

Sub AutoConcatenateDynamic()
    Dim ws As Worksheet
    Set ws = Sheet2
    
    Dim lastRow As Long, startCol As Long, endCol As Long
    lastRow = ws.UsedRange.Rows.Count
    startCol = ws.Range("W1").Column ' 起始列为W列
    endCol = ws.UsedRange.Columns(ws.UsedRange.Columns.Count).Column ' 自动获取最后一列
    
    Dim formulaParts As String
    Dim col As Long
    ' 遍历每一列，生成对应的IF判断片段
    For col = startCol To endCol
        formulaParts = formulaParts & "IF(ISBLANK(RC[" & col - startCol & "]), """", RC[" & col - startCol & "]&CHAR(10)),"
    Next col
    
    ' 移除末尾多余的逗号，组合完整公式
    formulaParts = Left(formulaParts, Len(formulaParts) - 1)
    Dim formulaStr As String
    formulaStr = "=CONCATENATE(" & formulaParts & ")"
    
    ' 应用公式并设置自动换行
    ws.Range("W2:W" & lastRow).FormulaR1C1 = formulaStr
    ws.Range("W2:W" & lastRow).WrapText = True
End Sub

核心优势：

• 完全兼容无TEXTJOIN的旧版本Excel
• 自动遍历从起始列到最后一列的所有列，新增列后无需手动调整公式

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