如何用VBA将AM4:AM20非空值按指定格式合并至BB4单元格

需求说明：将Excel工作表INPUT中AM4:AM20区域的非空单元格值合并到BB4，格式要求为前项用逗号分隔，最后一项用&连接，示例：ABCD1, ABCD2, ABCD3 & ABCD4（当前AM4=ABCD1、AM5=ABCD2、AM6=ABCD3、AM7=ABCD4，其余为空）。

原代码仅能实现所有非空值用逗号连接，无法满足最后一项用&连接的格式要求，以下是修改后的代码：

Sub GetPipeData()
    Dim ws As Worksheet
    Dim cell As Range
    Dim nonEmptyValues As Variant
    ReDim nonEmptyValues(0 To 0) '初始化数组存储非空值
    Dim i As Integer
    
    '指定目标工作表
    Set ws = ThisWorkbook.Sheets("INPUT")
    
    '遍历AM4:AM20区域，收集所有非空值到数组
    For Each cell In ws.Range("AM4:AM20")
        If Not IsEmpty(cell.Value) Then
            nonEmptyValues(UBound(nonEmptyValues)) = cell.Value
            ReDim Preserve nonEmptyValues(0 To UBound(nonEmptyValues) + 1)
        End If
    Next cell
    
    '移除数组末尾的空元素
    If UBound(nonEmptyValues) > 0 Then
        ReDim Preserve nonEmptyValues(0 To UBound(nonEmptyValues) - 1)
    End If
    
    Dim collectedValues As String
    '根据非空值数量拼接对应格式的结果
    Select Case UBound(nonEmptyValues) + 1
        Case 0 '无有效数据
            collectedValues = ""
        Case 1 '仅一个非空值
            collectedValues = nonEmptyValues(0)
        Case Else '多个非空值
            '先拼接前n-1个值为逗号分隔格式
            collectedValues = Join(nonEmptyValues, ", ")
            '替换最后一个逗号为&连接符
            collectedValues = Left(collectedValues, InStrRev(collectedValues, ", ")) & " & " & nonEmptyValues(UBound(nonEmptyValues))
    End Select
    
    '将结果写入BB4单元格
    ws.Range("BB4").Value = collectedValues
End Sub

核心修改说明： #

• 新增数组存储非空值，便于按数据量灵活处理格式
• 利用Join函数快速生成前n-1个值的逗号分隔字符串
• 通过InStrRev定位最后一个逗号的位置，替换为&完成最后一项的连接
• 覆盖了无数据、仅单个数据等边界场景

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