Taking multiple rows and bringing them to one row VBA

[burnsa9]

I am trying to take multiple rows and bring them to one row. for example I am trying to take address, City, State, and country and bring them to one column that has them all read. I cant use a concatnation excel formula becasue I need there to be spaces between address city state and country. An example of what my data looks like is below along with what I want my output to looklike after running the VBA. I have tried running the excel formula = A2&B2&C2&D2 however this doesn't allow for approprate spacing. [TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Address
[/TD]
[TD]City
[/TD]
[TD]State
[/TD]
[TD]Counrty
[/TD]
[TD]Combined (Output I am looking for)
[/TD]
[/TR]
[TR]
[TD]240 new Byhalia Rd
[/TD]
[TD]Memphis
[/TD]
[TD]Tennessee
[/TD]
[TD]United States
[/TD]
[TD]240 new Byhalia Rd Memphis Tennesse
[/TD]
[/TR]
</tbody>[/TABLE]

 

[MrKowz]

You can manually enter a space between each cell in the formula.

Try:

Excel 2013/2016
	A	B	C	D	E
1	Address	City	State	Counrty	Combined (Output I am looking for)
2	240 new Byhalia Rd	Memphis	Tennessee	United States	240 new Byhalia Rd Memphis Tennessee United States
Sheet2

Cell Formulas
Range	Formula
E2	=A2&" "&B2&" "&C2&" "&D2

 

[MrTeeny]

You can also add spaces by sticking in =A1&" "&B1&" "&C1&" "&D1 or whre you looking for some other format?