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!

 

[davesexcel]

I am not getting that error,

-

 

[Special-K99]

Doesn't #NAME mean it cant find the UDF?
Are you sure you put it in the correct place?
I don't know much about VBA/UDF, should it just be Function rather than Public Function?

See post #3 from Rick Rothstein, re UDF returning #NAME error

UDF returning #NAME error.

Good morning, I have a UDF that is returning a #NAME error, and I cannot figure out why. This is what I am doing(or trying): Averaging cells 1 and 2, then squaring it. Averaging cells 2 and 3, then squaring it. 3 and 4, 4 and 5, etc and summing all of them until the end of the row. Function...

www.mrexcel.com

 

[JJabra]

I am not getting that error,

-
View attachment 2093

That is odd, I also made a test sheet replicating what you have done and it is still coming up with a name error. Please let me know if you can see anything obvious that I'm doing incorrectly. also are you using the same code from my original post? As this is what I'm using

 

[Fluff]

Where is the code located?

 

[davesexcel]

the will not work if it is in the Worksheet module, a good indicator it is not in a regular module would be if you start typing =concat and the formula does not appear as you type.

 

[JJabra]

Ahh! thanks! For some reason I was putting it into "ThisWorkbook" having put it into its own module it is now working.

Thanks again all!

 

[Fluff]

Glad you sorted it & thanks for the feedback

 

[JJabra]

Hi Guys,

Sorry to call on you again, I have the below code that is in the same workbook which sends an email stating how many customers need a letter based on filters. this was working well until I added the ConcatenateVisible to also show which customers are needing letters in the email. however since I have added this into the cell that the email body pulls from, when I run the macro it comes up saying Automation Error. any ideas as to why this should be the case?

Private Sub EmailNotification()
    Dim a As Integer
    Dim objOutlook As Object
    Dim objMail As Object
    Dim rngTo As Range
    Dim rngSubject As Range
    Dim rngBody As Range
    Dim rngAttach As Range

    Set objOutlook = CreateObject("Outlook.Application")
    Set objMail = objOutlook.CreateItem(0)

    a = ActiveCell.Row

    With ActiveSheet
        Set rngTo = .Range("AB11")
        Set rngSubject = .Range("AB13")
        Set rngBody = .Range("AB12")
    End With

    With objMail
        .To = rngTo.Value
        .Subject = rngSubject.Value
        .Body = rngBody.Value
       '.Attachments.Add rngAttach.Value
        .Send

    End With

    Set objOutlook = Nothing
    Set objMail = Nothing
    Set rngTo = Nothing
    Set rngSubject = Nothing
    Set rngBody = Nothing
End Sub

 

[davesexcel]

Are you saying range("AB11") has the ConcatenateVisible() UDF? And you want to send the email to the results from AB11?

Just curious if you have tried using the ";" as the seperator?