Excel VBA循环获取每行最后数据列失效问题及动态拼接需求

我需要编写VBA脚本实现动态拼接：遍历A列的每一行，将表头为“Bundle Child x”的单元格值用;分隔拼接，结果写入对应行的A列。例如A2单元格的预期拼接结果为：AVS5-OD101-GL;BT022204-GL;BT022204-GL;BT01201-GL;BT01201-GL;BT031141-GL。

当前编写的代码如下：

Sub SpecialConcatenate()
Dim Sht As Worksheet
Dim lRow As Long
Dim lCol As Long
Dim iCell As Range

Set Sht = ActiveWorkbook.ActiveSheet
With Sht

    ' 获取B列最后一行数据行号
        lRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(Abs(.Cells(.Rows.Count, 1).End(xlUp).Value <> ""), 0).Row
    
    ' 遍历A列单元格执行拼接
        For Each iCell In Range("A2:A" & lRow)
        ' 获取当前行最后一列的列号
             lCol = .Cells(iCell.Row, .Columns.Count).End(xlToLeft).Column ' 此处出现问题
            
        Next iCell
    
End With

End Sub

代码无报错，但lCol变量无法随循环正确更新：第2、3行时lCol=7符合预期，但第4行的lCol应为4却未更新，请问这是什么原因？

原因分析 #

.Cells(iCell.Row, .Columns.Count).End(xlToLeft).Column 的问题在于：End(xlToLeft) 会从行最右侧开始向左查找第一个非空单元格，但如果当前行右侧存在残留格式（如曾输入内容后删除但格式保留）、隐藏的非空单元格，或者行尾有无关的空白但带格式的单元格，都会导致这个方法返回错误的列号。

此外，你的需求是拼接特定表头的列，没必要定位整行最后一列——直接匹配目标表头的列才是更准确、更健壮的做法，还能避免无关单元格干扰。

修正后的完整代码 #

以下是实现需求的完整代码，同时解决了lCol的问题：

Sub SpecialConcatenate()
    Dim Sht As Worksheet
    Dim lRow As Long
    Dim headerCell As Range
    Dim concatResult As String
    Dim currentRow As Long
    
    Set Sht = ActiveWorkbook.ActiveSheet
    With Sht
        ' 获取B列最后一行数据的行号
        lRow = .Cells(.Rows.Count, 2).End(xlUp).Row
        
        ' 遍历A列第2行到数据最后一行
        For currentRow = 2 To lRow
            concatResult = ""
            ' 遍历第一行的所有表头单元格
            For Each headerCell In .Rows(1).Cells
                ' 匹配表头为"Bundle Child x"的列
                If headerCell.Value Like "Bundle Child *" Then
                    ' 如果当前行该列非空，进行拼接
                    If .Cells(currentRow, headerCell.Column).Value <> "" Then
                        If concatResult <> "" Then concatResult = concatResult & ";"
                        concatResult = concatResult & .Cells(currentRow, headerCell.Column).Value
                    End If
                End If
            Next headerCell
            ' 将拼接结果写入当前行的A列
            .Cells(currentRow, 1).Value = concatResult
        Next currentRow
    End With
End Sub

代码说明 #

• 直接通过表头匹配规则"Bundle Child *"定位目标列，彻底避开了行尾无关单元格的干扰，从根源解决了lCol错误的问题
• 逐行逐个目标列判断非空后再拼接，确保结果符合需求
• 逻辑清晰，不受表格格式、隐藏单元格等因素影响，稳定性更强

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