Macro to add quotes, commas, transpose and select the transposed rows

[sncb]

Hi All,

I am constantly on a daily basis having to prepare data for SQL in excel so was wondering if there is an automatic way to add quotes, commas, transpose and select the transposed rows which i could simply paste into SQL.

Here's what I do:
1. I paste all my data in column B1 onwards
2. Add quotes A1 and C1
3. Add commas in D1
4. Concatenate in E1
5. Copy down the formula to all the rows of data
6. Copy column of data in E and paste values in F
7. Select all the values in F and Transpose into rows from cell G2 onwards.

Since I do this very often, is there a better way using a macro such that I can simply paste the column of data in B1 each time and run the macro so that it does the above steps without all the manual work involved?

Thanks for any assistance towards this manual effort.

 

[sncb]

Thanks Dreid. That worked perfectly well.

The output did have double quotes on the left side of each item that was transposed but I adjusted the macro accordingly.

Changed from combo(i) = "''" to combo(i) = "'"

Works great. Thank you for your time and effort and for easing my daily work.

Please ignore this part: Changed from combo(i) = "''" to combo(i) = "'"

I reset it back to your original code.

 

[Peter_SSs]

If you wanted to consider a non-looping version ..

Sub Test()
  Dim a As Variant
  a = Split("''" & Join(Application.Transpose(Range("B1", Range("B" & Rows.Count).End(xlUp)).Value), "',|''") & "'", "|")
  Range("G2").Resize(, UBound(a) + 1).Value = a
End Sub

 

[sncb]

If you wanted to consider a non-looping version ..

Sub Test()
  Dim a As Variant
  a = Split("''" & Join(Application.Transpose(Range("B1", Range("B" & Rows.Count).End(xlUp)).Value), "',|''") & "'", "|")
  Range("G2").Resize(, UBound(a) + 1).Value = a
End Sub

Hi Peter, Many thanks for your help as well. Your code works perfectly well too. thank you.

 

[dreid1011]

You're welcome, and happy to help.

 

[Peter_SSs]

Hi Peter, Many thanks for your help as well. Your code works perfectly well too. thank you.

Thanks for the follow-up. Glad to contribute.