EXCEL: Combine multiple cell string into a single cell using Macro VBA

I would like to combine multiple vertical cell in excell assuming from A1:A9 as shown below:

FROM:

    {'BAKWLC001': '10.144.250.240'}
    {'BEWLC002': '10.250.32.249'}
    {'CTALRwlc01': '10.52.188.100'}
    {'CTARXwlc01': '10.20.40.100'}
    {'CTB1wlc01': '10.65.224.20'}
    {'CTBCwlc01': '10.65.194.10'}
    {'CTBGKwlc01': '10.61.248.100'}
    {'CTBIRwlc01': '10.51.200.100'}
    {'CTC0wlc01-new': '10.61.205.100'}

INTO cell A10 as shown below. Which includes Enclosing everything with an Open/Close parenthesis and separating each item with a comma and space. Additionally, The last item shown above should dynamically have the Closing Parenthesis (Assuming there are not just 9 items as shown above. And let us say it has 10, 20, 500 items etc. the last item will have the closing parenthesis).

TO:

    [{'BAKWLC001': '10.144.250.240'}, {'BEWLC002': '10.250.32.249'}, {'CTALRwlc01': '10.52.188.100'}, {'CTARXwlc01': '10.20.40.100'}, {'CTB1wlc01': '10.65.224.20'}, {'CTBCwlc01': '10.65.194.10'}, {'CTBGKwlc01': '10.61.248.100'}, {'CTBIRwlc01': '10.51.200.100'}, {'CTC0wlc01-new': '10.61.205.100'}]

I have Tried using this VBA code:

    Range("A10") = Join(Application.Transpose(Range("A1:A9")), vbLf)

But it only combines all the cells BUT with no parentheses, comma in between, and specially the flexibility of having an enclose parenthesis on whatever item is in the end of the combined cell.

Solution

Try , comma instead of line feed vbLf. And add square bracket beginning and end of string after join.

Sub JoinText()
    Range("A10") = "[" & Join(Application.Transpose(Range("A1:A9")), ",") & "]"
End Sub

And TEXTJOIN() function for latest versions of Microsoft Excel.

="["&TEXTJOIN(",",1,A1:A9)&"]"

Edit: To ignore blank cells use Application.TextJoin().

Sub JoinText()
    'Range("A10") = "[" & Join(Application.Transpose(Range("A1:A9")), ",") & "]"
    Range("B1") = "[" & Application.TextJoin(",", 1, Range("A1:A500")) & "]"
End Sub