Excel VBA仅连接过滤柱的可见单元格。包含测试代码 [英] Excel VBA Concatenate only visible cells of filtered column. Test code included

问题描述

Good day all，

我试图将一个过滤的列连接成一个单元格，用逗号分隔。我不太了解编码，代码提供的是其他人在搜索几个小时后。

到目前为止，此函数的工作原理是连接不可见，过滤掉的单元格：

 函数test1（myRange As Range）
 Dim aOutput 
对于每个条目在myRange 
如果不是IsEmpty （entry.Value）然后
 aOutput = aOutput& entry.Value& 
 End If 
 Next 
 test1 = Left（aOutput，Len（aOutput） -  1）
结束函数
  pre $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ pre> 函数test2（ByRef rRng As Range，可选ByVal sDelim As String =，）As String 
 Dim oDict As Object 
 Dim rCell As Range 
 Dim sTxt As String 
设置oDict = CreateObject（Scripting.Dictionary）
与oDict 
对于每个rCell在rR $ 
如果.Exists（rCell.Text）然后
'不做任何
 Else 
。添加rCell.Text，rCell.Text 
 sTxt = sTxt& sDelim& rCell.Text 
结束如果
下一个rCell 
结束
 test2 = Mid（sTxt，Len（sDelim）+ 1）
结束函数
  

是否可以更改两个函数来忽略列中不可见，过滤的单元格？

感谢阅读，

Brian

解决方案

p>考虑：

 公共函数test1（myRange As Range）
 Dim aOutput As String，entry As Range 
对于每个条目在myRange 
如果entry.EntireRow.Hidden = False然后
 aOutput = aOutput& entry.Value& 
 End If 
 Next 
 test1 = Left（aOutput，Len（aOutput） -  1）
结束函数
  pre> 
Good day all,

I'm trying to concatenate a filtered column into a single cell separated with commas. I know little about coding, the code provided are by others after hours of searching.

So far, this function works but also concatenates invisible, filtered out cells:
Function test1(myRange As Range)
Dim aOutput
For Each entry In myRange
    If Not IsEmpty(entry.Value) Then
        aOutput = aOutput & entry.Value & ", "
    End If
Next
test1 = Left(aOutput, Len(aOutput) - 1)
End Function
And this one works well where it will also remove duplicates from the range, but has the same problem:
Function test2(ByRef rRng As Range, Optional ByVal sDelim As String = ", ") As String
Dim oDict As Object
Dim rCell As Range
Dim sTxt As String
Set oDict = CreateObject("Scripting.Dictionary")
With oDict
    For Each rCell In rRng
        If .Exists(rCell.Text) Then
            'Do nothing
        Else
            .Add rCell.Text, rCell.Text
            sTxt = sTxt & sDelim & rCell.Text
        End If
    Next rCell
End With
    test2 = Mid(sTxt, Len(sDelim) + 1)
End Function
Is it possible to alter the two functions to ignore invisible, filtered out cells in a column?

Thanks for reading,

Brian
解决方案
Consider:
Public Function test1(myRange As Range)
    Dim aOutput As String, entry As Range
    For Each entry In myRange
        If entry.EntireRow.Hidden = False Then
            aOutput = aOutput & entry.Value & ", "
        End If
    Next
    test1 = Left(aOutput, Len(aOutput) - 1)
End Function


                        
这篇关于Excel VBA仅连接过滤柱的可见单元格。包含测试代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！