How to create list of string with special format in excel by VBA

Question

I have column in excel with values like this:

Column_Name
row1
row2
row3

and I need create a string variable from that by VBA in this format

('row1','row2','row3')

I have this code

   Dim Strg As String
   With Range(Range("E2"), Range("E2").End(xlDown))
      Strg = Replace(Application.Trim(Join(Application.Transpose(.Value), " ")), " ", ",")
   End With

But it gives me only row1,row2,row3 I don't know How to put it in between ' ' and add parentheses to the beginning and end. Do you have any idea?

Siddharth Rout · Accepted Answer · 2021-02-19 10:39:12Z

2

Is this what you are trying?

Dim Strg As String
With Range(Range("E2"), Range("E2").End(xlDown))
    Strg = Replace(Application.Trim(Join(Application.Transpose(.Value), " ")), " ", "','")
End With

Debug.Print "('" & Strg & "')"

answered Feb 19, 2021 at 10:39

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Winter Over a year ago

No, this give me ('row1,row2,row3') and i need ('row1','row2','row3'). The ' must be between each row, not just begginig and end

Siddharth Rout Over a year ago

Are you sure you copied the code as I pasted it? I think you missed the the "','" part :D

Collectives™ on Stack Overflow

How to create list of string with special format in excel by VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related