如何用VBA实现Concatenate与IF函数的自动化拼接公式？

原需求目标公式 #

需要实现的Excel公式（自动拼接W-Z列非空单元格内容，空单元格忽略，非空内容后加换行）：

=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)))

原代码问题分析 #

1. 最后一行行号获取错误：Sheet1.UsedRange.Count返回的是已使用区域的单元格总数，不是目标列（W列）的最后一行行号，会导致范围计算错误。
2. 赋值范围错误：试图给$W$2:$Z区域赋值公式，会覆盖原数据，且不符合“拼接结果输出”的需求。
3. 公式传递格式错误：直接在VBA中写Excel函数语法，未将公式转为字符串；字符串内的双引号未转义；原公式缺少闭合的右括号。

修正后的VBA代码 #

Sub ConcatenateWithIF()
    Dim ws As Worksheet
    Set ws = Sheet1 ' 指定目标工作表
    
    Dim lrow As Long
    ' 获取W列最后一行行号（避免空行干扰）
    lrow = ws.Cells(ws.Rows.Count, "W").End(xlUp).Row
    
    ' 定义公式字符串：注意双引号转义（用两个""代替"），补全闭合括号
    Dim formulaStr As String
    formulaStr = "=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)))"
    
    ' 将公式批量写入AA列（可根据需求修改目标列）
    ws.Range("AA2:AA" & lrow).Formula = formulaStr
    
    ' 开启自动换行，让CHAR(10)的换行效果生效
    ws.Range("AA2:AA" & lrow).WrapText = True
End Sub

关键说明 #

• 目标列选择：代码中把拼接结果写入AA列，可根据实际需求修改为其他列（比如修改为ws.Range("AB2:AB" & lrow)）。
• 行号定位：使用Cells(Rows.Count, "W").End(xlUp).Row精准定位W列最后一行，避免UsedRange的不确定性。
• 双引号转义：VBA字符串中，若要表示Excel公式里的双引号，必须用两个连续双引号（""）转义，否则会触发语法错误。
• 换行显示：必须开启单元格自动换行，否则CHAR(10)的换行符只会显示为空格。

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