3 columns into 1 column

usl

Board Regular
Joined
Jan 28, 2005
Messages
71
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
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
If the names are in A1, B1 and C1. In D1 put:

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

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
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

Board Regular
Joined
Jan 28, 2005
Messages
71
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,421
Messages
5,596,033
Members
414,039
Latest member
southike

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top