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

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

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" <PVANS@discussions.microsoft.com> wrote in message news:9396295E-1A92-4FB9-9DCA-4BC1B7B0460C@microsoft.com...
> 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

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

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