CODEWITHSUNDEEP
excelvbalist
Winter
Winter

Reputation: 87

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

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?

Upvotes: 1

Views: 67

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149315

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 & "')"

Upvotes: 2

Related Questions