Moving multiple cells in a coulmn to one cell

[s0nicstang]

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!

 

[prabby25101981]

I think a macro like this should do -

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]

Or...
Assumes your dates are in column A, starting at row 2 and you want the end product in B1. (same as prabby assumed.)

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.

 

[prabby25101981]

Nice !

 

[JoeMo]

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:

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

 

[s0nicstang]

You guys are awesome all of them work great thanks!!

 

[HalfAce]

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.