Merge 1000+ cells into one
 

Good morning

I am really hoping someone can help me with this.

I have a worksheet with 1 column (A) and 1439 rows. I need to combine all
1439 of these cells into one, using the following format:
A1 & "" & A2 & "" etc.

Naturally, doing this manually would take several hours (and be extremely
vulnerable to mistakes.) Is there a method using VBA that I could do this?

Thanks for any ideas.

Paul

Merge 1000+ cells into one
 

Hi,

Try this

Sub pvans()
Dim TempString As String
Set sht = Sheets("Sheet1")
Set MyRange = sht.Range("A1:A1439")
For Each c In MyRange
TempString = TempString & " " & c.Value
Next
Range("B1") = Trim(TempString)
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"PVANS" wrote:

Good morning

I am really hoping someone can help me with this.

I have a worksheet with 1 column (A) and 1439 rows. I need to combine all
1439 of these cells into one, using the following format:
A1 & "" & A2 & "" etc.

Naturally, doing this manually would take several hours (and be extremely
vulnerable to mistakes.) Is there a method using VBA that I could do this?

Thanks for any ideas.

Paul

Merge 1000+ cells into one
 

Hi Paul,

For Column A, from row 1 to the last used row, try:
Sub Concat()
Dim MyString As String, i As Integer
With ActiveSheet
For i = 1 To .Range("A65536").End(xlUp).Row
MyString = MyString & """" & .Cells(i, 1).Value & """"
Next
.Cells(i, 1).Value = MyString
End With
End Sub

--
Cheers
macropod
[Microsoft MVP - Word]


"PVANS" wrote in message ...
Good morning

I am really hoping someone can help me with this.

I have a worksheet with 1 column (A) and 1439 rows. I need to combine all
1439 of these cells into one, using the following format:
A1 & "" & A2 & "" etc.

Naturally, doing this manually would take several hours (and be extremely
vulnerable to mistakes.) Is there a method using VBA that I could do this?

Thanks for any ideas.

Paul

Merge 1000+ cells into one
 

Thanks Mike,

Brilliant as always, works perfectly.

Thanks too you to macropod - really appreciate the assistance

Cheers, Have a nice day

"Mike H" wrote:

Hi,

Try this

Sub pvans()
Dim TempString As String
Set sht = Sheets("Sheet1")
Set MyRange = sht.Range("A1:A1439")
For Each c In MyRange
TempString = TempString & " " & c.Value
Next
Range("B1") = Trim(TempString)
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"PVANS" wrote:

Good morning

I am really hoping someone can help me with this.

I have a worksheet with 1 column (A) and 1439 rows. I need to combine all
1439 of these cells into one, using the following format:
A1 & "" & A2 & "" etc.

Naturally, doing this manually would take several hours (and be extremely
vulnerable to mistakes.) Is there a method using VBA that I could do this?

Thanks for any ideas.

Paul

Merge 1000+ cells into one
 

Glad i could help and thanks for the feedback
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"PVANS" wrote:

Thanks Mike,

Brilliant as always, works perfectly.

Thanks too you to macropod - really appreciate the assistance

Cheers, Have a nice day

"Mike H" wrote:

Hi,

Try this

Sub pvans()
Dim TempString As String
Set sht = Sheets("Sheet1")
Set MyRange = sht.Range("A1:A1439")
For Each c In MyRange
TempString = TempString & " " & c.Value
Next
Range("B1") = Trim(TempString)
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"PVANS" wrote:

Good morning

I am really hoping someone can help me with this.

I have a worksheet with 1 column (A) and 1439 rows. I need to combine all
1439 of these cells into one, using the following format:
A1 & "" & A2 & "" etc.

Naturally, doing this manually would take several hours (and be extremely
vulnerable to mistakes.) Is there a method using VBA that I could do this?

Thanks for any ideas.

Paul