3 columns into 1 column

[usl]

Hello,

I need a little help.

I have some citynames in the columns A1:C20, thoes names I would like to have in the column E E1:E60. Of course I cna just do copy and paste, but this is only an example.
(I don´t mean the function: concatenate) I don´t want to combine the names I still want them seperated but all in one column.

Does anyone know how to du this ?

Thanks a lot in advance fro your help.

BR
Uli

 

[Joe Was]

If the names are in A1, B1 and C1. In D1 put:

=A1 & " " & B1 & " " & C1

 

[Von Pookie]

This works for me:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> test()

<SPAN style="color:#00007F">Dim</SPAN> myCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, PasteRange <SPAN style="color:#00007F">As</SPAN> Range

myCol = Array("A", "B", "C")
<SPAN style="color:#00007F">Set</SPAN> PasteRange = [E1]
<SPAN style="color:#00007F">Do</SPAN>
    Range(Cells(1, myCol(i)), Cells(65536, myCol(i)).End(xlUp)).Copy _
    Destination:=PasteRange
    i = i + 1
    <SPAN style="color:#00007F">Set</SPAN> PasteRange = [E65536].End(xlUp).Offset(1, 0)
<SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> i < 3

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[usl]

THX, the VBA works great, but one question, if I want to specife the Array so that it will only red in a surtain area (like A1:A50, B1:B50,C1:C50)

then I get an run-time error in this section

_____
Do
Range(Cells(1, myCol(i)), Cells(65536, myCol(i)).End(xlUp)).Copy _
Destination:=PasteRange
_____

What do I have to change here ?

Thanks a lot in advance for you help again.

BR
Uli

 

[Von Pookie]

Would this be better? You can specify the range you want with the inputbox.

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> test2()
<SPAN style="color:#00007F">Dim</SPAN> myCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, PasteRange <SPAN style="color:#00007F">As</SPAN> Range
<SPAN style="color:#00007F">Dim</SPAN> ans <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>, x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>

<SPAN style="color:#00007F">Set</SPAN> ans = Application.InputBox("Enter or select the desired range:", _
    Type:=8)

<SPAN style="color:#00007F">If</SPAN> ans <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Set</SPAN> PasteRange = [E1]
i = 1

<SPAN style="color:#00007F">Do</SPAN>
    x = ans.Columns(i).Cells.Count
    Range(ans.Cells(1, i), ans.Cells(x, i)).Copy _
    Destination:=PasteRange
    i = i + 1
    <SPAN style="color:#00007F">Set</SPAN> PasteRange = [E65536].End(xlUp).Offset(1, 0)
<SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> i <= ans.Columns.Count

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[usl]

PERFECT !!

Thanks to Von Pookie.



BR
Uli