Excel VBA功能将非空单元格与用户定义的分隔符连接起来 [英] Excel VBA function to concatenate non-empty cells with a user defined seperator

问题描述

我发现这个代码：

Function ConcatenateRange(ByVal cell_range As Range, _
                    Optional ByVal seperator As String) As String

Dim cell As Range
Dim newString As String
Dim cellArray As Variant
Dim i As Long, j As Long

cellArray = cell_range.Value

For i = 1 To UBound(cellArray, 1)
    For j = 1 To UBound(cellArray, 2)
        If Len(cellArray(i, j)) <> 0 Then
            newString = newString & (seperator & cellArray(i, j))
        End If
    Next
Next

If Len(newString) <> 0 Then
    newString = Right$(newString, (Len(newString) - Len(seperator)))
End If

ConcatenateRange = newString

End Function

它将一个范围组合成一个单元格（ CONCATENATES 单元格，并在每个部分之间添加一个空格）。我已经尝试了很多次来编辑它，以在每个项目之间添加一个，。问题是它引用范围 A1：A1000 ，其中我只能使用10行或全部。我不想让它有额外的,,,,,,在组合结束时，我没有填充每个单元格。

It combines a range into one cell (CONCATENATES the cells and adds a space between each part). I have tried many times in vain to edit it to add a "," between each item instead. The issue is that it references the range A1:A1000, where I may only use 10 Rows or all of them. I just dont want it to have extra ,,,,,, at the end of the combine for each cell I didnt fill.

也想创建这个它将在每个项目的右侧添加一个; 。

Would also like to create duplicate of this where it would add a ; to the right of each item.

如何编辑这个添加这些零件，左或右，但只能填充单元格。

How do I edit this to add those parts, either left or right but only for the filled cells.

感谢您的帮助。

推荐答案

您的宏已经有效了。 = ConcatenateRange（A1：A14，，）其中 A1 至 A4 中有数字1-4，你会得到 1,2,3,4 。

Your Macro already works. =ConcatenateRange(A1:A14,",") where A1 to A4 have numbers 1-4 in them, you'll get 1,2,3,4.

这篇关于Excel VBA功能将非空单元格与用户定义的分隔符连接起来的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！