Excel:连接列中的最后一个非空单元格

Question

由于我的previous post已关闭，但问题仍然存在，我将在此处重新表述。我想出了以下几点:

Function JoinLastInColIfEmpty(range_ As Range, delim_ As String)
    Dim cell As Range, result As String, current As String

    For Each cell In range_
        current = LastNonEmptyInCol(cell)
        If current <> "" Then
            result = result & current & delim_
        End If
    Next

    If Not IsEmpty(result) Then
        result = Left(result, Len(result) - Len(delim_))
    End If

    JoinLastInColIfEmpty = result
End Function

Function LastNonEmptyInCol(cell_ As Range)
    Dim tmp As Range
    tmp = cell_ '<< The problem occurs here
    Do Until Not IsEmpty(tmp) Or tmp.Row = 1
        tmp = tmp.Offset(-1, 0)
    Loop
    LastNonEmptyInCol = tmp.Value
End Function

问题是函数永远不会结束，所以我的问题是:

我的脚本有什么问题？

我该怎么解决my problem ?

Answer 1

为了回答您的直接问题，LastNonEmptyInCol 中有几个错误。

Function LastNonEmptyInCol(cell_ As Range)
    On Error Resume Next
    Dim tmp As Range
    Set tmp = cell_ '<< The problem occurs here  ' <<<<< use Set
    Do Until Not IsEmpty(tmp) Or tmp.Row = 1     ' <<<<< use tmp not cell_
        Set tmp = tmp.Offset(-1, 0)              ' <<<<< use Set 
    Loop
    LastNonEmptyInCol = tmp.Value
End Function

也就是说，我认为这是一个非常低效的解决方案，并不能完全解决您所说的问题

结果将是

    A  |  B  |  C  |  D  | Concat
  -----+-----+-----+-----+---------
    1  |  2  |  X  |  5  | 12X5
       |     |  f  |  3  | 12f3
       |  5  |  R  |  12 | 15R12
    Z  |  3  |  T  |     | Z3T12
       |  G  |     |     | ZGT12

这是另一个可能更好的版本

Function MyJoinLastInColIfEmpty(range_ As Range, delim_ As String)
    Dim vData As Variant
    Dim cl As Range
    Dim i As Long
    Dim result As Variant

    vData = range_

    For i = 1 To UBound(vData, 2)
        If vData(1, i) = "" Then
            Set cl = range_.Cells(1, i).End(xlUp)
            If cl <> "" Then
                vData(1, i) = cl.Value
            End If
        Else
            Exit For
        End If
    Next
    For i = 1 To UBound(vData, 2)
        result = result & vData(1, i) & delim_
    Next

    MyJoinLastInColIfEmpty = Left(result, Len(result) - Len(delim_))
End Function