CONCATENATE but list is too long. VBA needed?

[kiefi]

Hi all
I have a list of numbers in column A (list is dynamically loaded from a data base) which I need to concatenate (comma separated) into one cell, let's say B2. This could look something like:

A B
305 305,407,809
407
809

I have 2 issues:
1) because the list in column A is too long, I can't use the concatenate formula (excel simply can't handle it and gives me an error)
2) because the list is loaded from a data base, excel formulas change to #REF ! after reloading the data

I guess I need to switch to a VBA solution but so far couldn't figure out the correct code. Hope somebody can help. Thanks!

 

[Fennek]

Hallo,

try this code (untested)

sub iConcat()
lr = cells(rows.count,1).end(xlup).row
cells(1,2) =join(application.transpose(Range("A1:A" & lr), ", ")
end sub

regards

 

[kiefi]

Unfortunately I get a syntax error with this code. Is there something missing?

 

[njimack]

I wrote this sometime ago because the native function is so unwieldy...

Function Concatenate_Range(myrange As Range, Optional myDelimiter As String)
Dim Cell As Range
Application.Volatile
For Each Cell In myrange
    If Len(Cell.Value) > 0 Then
        Concatenate_Range = Concatenate_Range & Cell & myDelimiter
    Else: Concatenate_Range = Concatenate_Range
    End If
Next Cell
If Len(myDelimiter) > 0 Then Concatenate_Range = Left(Concatenate_Range, Len(Concatenate_Range) - Len(myDelimiter))
End Function

 

[kiefi]

Hi Neil. Thanks for that. I couldn't make it work yet. Do you maybe have the excel file you use this function in? This would make it easier for me to understand how to integrate it into mine. Thank you!

 

[Fennek]

at least 1 bracket is missing

sub iConcat()
lr = cells(rows.count,1).end(xlup).row
cells(1,2) =join(application.transpose(Range("A1:A" & lr), ", "))
end sub

 

[njimack]

In your sample data, you would enter the following into B1:
=Concatenate_Range(A1:A3,",")

 

[steve the fish]

at least 1 bracket is missing

sub iConcat()
lr = cells(rows.count,1).end(xlup).row
cells(1,2) =join(application.transpose(Range("A1:A" & lr), ", "))
end sub

It should be:

cells(1,2) =join(application.transpose(Range("A1:A" & lr)), ", ")