Do while VB Macro

[sanj_shah]

Hi,

I have a worksheet like below:

I have separated each column with a comma below for the layout in this post ie.
column1,column2,column3

aaa1,bb1,ccc1
, ,ccc2
, ,ccc3
, ,ccc4
, ,ccc5
,bb2,ccc1
, ,ccc2
, ,ccc3
, ,ccc4
,bb3,ccc1
, ,ccc2
, ,ccc3
aaa2,bb1,ccc1
, ,ccc2
, ,ccc3
, ,ccc4
, ,ccc5
,bb2,ccc1
, ,ccc2
, ,ccc3
, ,ccc4
aaa3,bb1,ccc1
, ,ccc2
, ,ccc3
, ,ccc4
, ,ccc5

etc

what I would like is to create the following value to be pasted into another
cell (i.e. A1000)

aaa1=["bb1","ccc1","ccc2","ccc3","ccc4","ccc5","bb2","ccc1","ccc2","ccc3","c
cc4","bb3","ccc1","ccc2","ccc3"]&aaa2=["bb1","ccc1","ccc2","ccc3","ccc4","cc
c5","bb2","ccc1","ccc2","ccc3","ccc4"]&aaa3=["bb1","ccc1","ccc2","ccc3","ccc
4","ccc5"];

I tried to use the do while loop but cannot get this to work, could someone
please point me in the right direction.

Thanks!

Sanj

 

[Scott Huish]

Give this a try:

Sub test()
Dim x As Long, tmp As String
For x = 1 To Range("C65536").End(xlUp).Row + 1
    If Range("A" & x) <> "" Then tmp = tmp & "&" & Range("A" & x) & "=["
    If Trim(Range("B" & x)) <> "" Then tmp = tmp & Chr(34) & Trim(Range("B" & x)) & Chr(34) & " "
    If Trim(Range("C" & x)) <> "" Then tmp = tmp & Chr(34) & Trim(Range("C" & x)) & Chr(34) & " "
    If Range("A" & x).Offset(1, 0) <> "" Then tmp = tmp & "]"
Next x
tmp = Replace(Replace(Application.Trim(tmp), " ", ", "), ", ]", "]") & "];"
Range("A1000") = tmp
End Sub