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]

yes sorry, I added as entered above and I was still getting the value error

 

[davesexcel]

You may have to link up an example wb , I don't know how to replicate you error.

 

[JJabra]

Example workbook

I have linked an example version of the workbook removing customer details etc. please let me know if this helps to understand the error

 

[davesexcel]

you are combining two functions into one, I don't have the first function you are using on my computer. I get _xlfun error

Possibly using & then the other function instead of putting it in the same concatenate function will stop the automation error.
=CONCATENATE("There are ",AB1, " eight-week letters to send. The customers that need 8-week letters are: ") & ConcatenateVisible(A2:A513, ", ")

The #value still appears after you click the filter button, I noticed pressing F9 to calculate the sheet the formula fixes itself.
You can tell the sheet to calculate after the filter codes have run.

Sub MacroButton()

    Call Escalations
    Call Resolved
    Call LetterSent
    Call Ombudsman
    Call EmailNotification
    Calculate
End Sub

 

[JJabra]

You are a hero!

I have modified the code to include the & and also added calculate before the email sends and it now works great!

Thank you very much for all the help and persisting with it!