Moving multiple cells in a coulmn to one cell

s0nicstang

Board Regular
Joined
Jan 7, 2009
Messages
73
I have a list of dates going down my coulmn and would like to move them all into one cell seperated by a space. right now i am using the =A1&" "&A2.... but i only typed it out for 100 cells and need way more. Is there a simple macro that can do this?

here is what i need

Dates
08032010
08022010
08012010
07302010
07292010

End product : 08032010 08022010 08012010 07302010 07292010

Need them all in one very long cell to copy and paste the string of dates into an import program.

Any ideas?

Thanks!
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

prabby25101981

Active Member
Joined
Jul 28, 2010
Messages
348
I think a macro like this should do -

Code:
Sub MergeDates()
Dim LRow As Long
Dim ResStrng As String
LRow = Range("A65536").End(xlUp).Row
For i = 2 To LRow
    ResStrng = Trim(ResStrng & " " & Range("A" & i).Value)
Next
Range("B1").Value = ResStrng
End Sub
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
Or...
Assumes your dates are in column A, starting at row 2 and you want the end product in B1. (same as prabby assumed.)
Code:
Sub ConcatAll()
Dim Lr As Long, r As Range
Lr = Cells(Rows.Count, "A").End(xlUp).Row
If Lr = 2 Then Range("B1").Value = Range("A2").Value: Exit Sub
For Each r In Range("A2:A" & Lr)
  If r.Row = 2 Then Range("B1").Value = r.Value Else _
    Range("B1").Value = Range("B1").Value & ", " & r.Value
Next r
End Sub

Hope it helps.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,933
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

I have a list of dates going down my coulmn and would like to move them all into one cell seperated by a space. right now i am using the =A1&" "&A2.... but i only typed it out for 100 cells and need way more. Is there a simple macro that can do this?

here is what i need

Dates
08032010
08022010
08012010
07302010
07292010

End product : 08032010 08022010 08012010 07302010 07292010

Need them all in one very long cell to copy and paste the string of dates into an import program.

Any ideas?

Thanks!
If you have a large number of cells, this may be a bit faster. Assumed your data starts in A1 and the output goes to C1:
Code:
Sub ListDates()
Dim vDates As Variant, sDates As String, LR As Long

LR = Cells(Rows.Count, 1).End(xlUp).Row
vDates = Range("A1", "A" & LR).Value

For i = 1 To UBound(vDates, 1)
sDates = sDates & " " & Format(CStr(vDates(i, 1)), "0#######")
Next i
sDates = Right(sDates, Len(sDates) - 1)
Range("C1").Value = sDates
End Sub
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
all of them work great
One of the things I like most about this site. I enjoy seeing, (not just how many different ways the same thing can be accomplished - which is also cool), but how many different thought processes are used to achieve the same objective. :cool:
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,111
Messages
5,857,448
Members
431,880
Latest member
kar2rost

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