Concatenate Visible

[JJabra]

Hi,

I am looking to write a formula that will include into one cell all the values that are visible in column A after filtering. Looking online I have only been able to find the below;

Public Function ConcatenateVisible(rng As Variant, seperator As String, Optional IGNORE_EMPTY As Boolean = False)
    For Each cll In rng
        If cll.EntireRow.Hidden = False And Not (IsEmpty(cll) And IGNORE_EMPTY) Then _
            ConcatenateVisible = ConcatenateVisible & cll.Value & seperator
    Next
    ConcatenateVisible = Left(ConcatenateVisible, Len(ConcatenateVisible) - Len(seperator))
End Function

however when I now try to use the ConcatenateVisible formula it brings a #NAME? error.

Please can somebody advise of another way to do this or advise how to make the current macro/formula work?

Thanks in advance!

 

[JJabra]

The ConcatenateVisible is in the body, Cell "AB12" the email is going to a work inbox to notify which customers need to have emails sent to them via post.

 

[davesexcel]

i see.
I have tested out the code with your concate UDF and do not get an error

 

[JJabra]

I have noticed that the issue seems to be that I have a button that filters the table and then sends the email, however when the table is filtered, the ConcatenateVisible returns #VALUE!. However when I go on to the cell and press enter the error goes and I can then run the macro to send the email.

Do you know of any way in which this can work without me having to manually fix the value error?

 

[davesexcel]

I can't replicate the problem, possible application.volatile would work.

Public Function ConcatenateVisible(rng As Variant, seperator As String, Optional IGNORE_EMPTY As Boolean = False)
    Application.Volatile
    For Each cll In rng
        If cll.EntireRow.Hidden = False And Not (IsEmpty(cll) And IGNORE_EMPTY) Then _
           ConcatenateVisible = ConcatenateVisible & cll.Value & seperator
    Next
    ConcatenateVisible = Left(ConcatenateVisible, Len(ConcatenateVisible) - Len(seperator))
End Function

 

[JJabra]

That didn't stop me from getting the #Value! issue when filtering the table, any other Ideas as to why this happens?

Also is there a way to stop the comma appearing on empty rows? as I have set the list to 500 rows because the size of the extract used to populate the table can vary daily.

 

[JJabra]

I have managed to fix the issues with the commas by setting the reference to the column in the table, however I am still having issues with the ConcatenateVisible returning a #Value! error when I run my autofilter macro.

This is the last issue I have. Any help would be greatly appreciated

 

[davesexcel]

Ignore empty may be dimmed as a string, not true/false
When I enter strings to ignore it does

Public Function ConcatenateVisible(rng As Variant, seperator As String, Optional IGNORE_EMPTY As String)
    Application.Volatile
  
    For Each cll In rng
        If cll.EntireRow.Hidden = False Then
            If Not IsEmpty(cll) Then
                If cll <> IGNORE_EMPTY Then
                    ConcatenateVisible = ConcatenateVisible & cll.Value & seperator
                End If
            End If
        End If
    Next
    ConcatenateVisible = Left(ConcatenateVisible, Len(ConcatenateVisible) - Len(seperator))
End Function

Here i told it to ignore g and it did

 

[davesexcel]

Back to one if line

Public Function ConcatenateVisible(rng As Variant, seperator As String, Optional IGNORE_EMPTY As String)
    Application.Volatile
    For Each cll In rng
        If cll.EntireRow.Hidden = False And Not IsEmpty(cll) And cll <> IGNORE_EMPTY Then _
                                                             ConcatenateVisible = ConcatenateVisible & cll.Value & seperator
    Next
    ConcatenateVisible = Left(ConcatenateVisible, Len(ConcatenateVisible) - Len(seperator))
End Function

 

[JJabra]

sorry, I'm not sure I fully understand, I wouldnt need to miss out certain entries, however when running the below macros to filter the data set to customers that will need to be contacted it comes up with a value! error

Sub Escalations()
    
    Dim sht As Worksheet
    Set sht = Worksheets("Extract")

    With sht.Range("C1").CurrentRegion
        .AutoFilter
        .AutoFilter Field:=3, Criteria1:="<" & CLng(Date - 56)
        
    End With
End Sub

Sub LetterSent()
    
    Dim lo As ListObject
    Dim iCol As Long

    Set lo = Sheet2.ListObjects(1)
    iCol = lo.ListColumns("Date Of Eight Week Letter Sent").Index
    lo.Range.AutoFilter Field:=iCol, Criteria1:="="
    
End Sub

Sub Resolved()
    
    Dim lo As ListObject
    Dim iCol As Long

    Set lo = Sheet2.ListObjects(1)
    iCol = lo.ListColumns("Resolved").Index
    lo.Range.AutoFilter Field:=iCol, Criteria1:="N"
    
End Sub

Sub Ombudsman()
    
    Dim lo As ListObject
    Dim iCol As Long

    Set lo = Sheet2.ListObjects(1)
    iCol = lo.ListColumns("RAISED TO OMBUDSMAN").Index
    lo.Range.AutoFilter Field:=iCol, Criteria1:="N"
    
End Sub

 

[davesexcel]

So does this mean you tried my suggested code and it did not work?