☰

仅连接唯一值

tag-icon • tag-icon microsoft-excel vba
仅连接唯一值

我有这个功能

Public Function CONCATENATESPECIAL(rng As Range) As String
Dim rng1 As Range

CONCATENATESPECIAL = ""

For Each rng1 In rng
    If (Not Rows(rng1.Row).Hidden) And (rng1.Value <> "") Then
    CONCATENATESPECIAL = CONCATENATESPECIAL & rng1.Text & "|"
    End If
Next rng1

End Function

它基本上用“|”将某个范围内的值连接起来。

我需要的是 - 是否可以编辑它，如果在该范围内有一些重复的值，是否只包含一次？总而言之 - 仅连接唯一值。

提前致谢

答案1

我可能会这样做。我会将所有内容添加到集合中，并将键设置为等于范围值。on error resume next当它尝试将重复项放入集合中时，该位将阻止它出错。我需要更多验证，但在大多数情况下，这应该足够了。

Public Function concatenatespecial(rng As Range) As String

Dim col As New Collection
Dim rng1 As Range
Dim str As String
Dim itm As Variant

On Error Resume Next
For Each rng1 In rng
    If rng1 <> "" Then
        col.Add rng1.Value, rng1.Value
    End If
Next rng1
On Error GoTo 0

For Each itm In col
    str = str & "|" & itm
Next itm

concatenatespecial = str

End Function

相关内容