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


0




Reply

Utf

3/23/2010 9:59:01 AM 

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


0




Reply

Utf

3/23/2010 10:27:02 AM


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:9396295E1A924FB99DCA4BC1B7B0460C@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


0




Reply

macropod

3/23/2010 10:29:52 AM


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


0




Reply

Utf

3/23/2010 11:32:02 AM


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


0




Reply

Utf

3/23/2010 11:40:01 AM



4 Replies
293 Views
(page loaded in 0.053 seconds)
