Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
divide many cells by 1000 | Excel Discussion (Misc queries) | |||
Dividing multiple cells by 1000 | Excel Worksheet Functions | |||
Divide Cells on a spreadsheet by 1000 | Excel Discussion (Misc queries) | |||
Dividing cells by 1000 | Excel Discussion (Misc queries) | |||
Multiples of 1000 in cells? | Excel Discussion (Misc queries) |