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!
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

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,453
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,094
Office Version
365, 2010
Platform
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,453
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:
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,635
Messages
5,512,536
Members
408,902
Latest member
VicRattlehead

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top