General discussion

Locked

MS Excel question...

By New Graduate ·
How do I copy all cells w/i a column to a single cell. For example, I need to copy the values in cells A1-A10 into a single, comma delimited cell on another spreadsheet. Can this be done? Thanks in advance for any assistance.

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

MS Excel question...

by tclere In reply to MS Excel question...

I am not sure that there is a simple copy/paste method for this. The best solution will depend on your specific application. Here are a couple simple solutions.

1. Put this formula in the destination cell

=Sheet1!A1 & "," & Sheet1!A2 & "," & Sheet1!A3 etc.


2. Use a macro to do the dirty work

Sub CopyToCell()
Dim sVal As String, i As Integer
Dim Sht1 As Worksheet, Sht2 As Worksheet

Set Sht1 = Worksheets("Sheet1")
Set Sht2 = Worksheets("Sheet2") sVal = Sht1.Range("A1").Text
For i = 2 To 10
sVal = sVal & "," & Sht1.Range("A" & i).Text
Next i

Sht2.Range("A1").FormulaR1C1 = sVal
End Sub

Hope this gets you in the right direction.

Collapse -

MS Excel question...

by tclere In reply to MS Excel question...

It has been a while. Have you solved this problem yet? You may want to close the thread.

Collapse -

MS Excel question...

by New Graduate In reply to MS Excel question...

The question was auto-closed by TechRepublic

Collapse -

MS Excel question...

by srobertson In reply to MS Excel question...

If the cell range will remain perpetually static or at least change irregularly then your best option would be to use a simple formula in the destination cell such as :-

=Sheet1!A1&","&Sheet1!A2&","&Sheet1!A3....Etc

However if the cell range fluctuates in size it would be easier to use a macro to copy the values.

Sub MergeCellValues ()
'Declare Variable
Dim rngTestCell as Range
Dim strNewCell as String

'Activate Source Worksheet
Thisworkbook.Sheets(1).Activate
Range("A1").Select

'Test for more than One Value and step to end of List
If ActiveCell.Offset(1,0) <> "" Then Selection.End(xlDown).Select

'Concatenation Loop
For Each rngTestCell in Range("A1:A" & Activecell.Row)
' Add New Cell values & Delimiter
strNewCell = strNewCell & rngTestCell.Value & ","

Next rngTestCell

'Set new cell value stripping last delimiter
Thisworkbook.Sheets(2).Range("A1").Value=Left(strNewCell,Len(strNewCell)-1)

End Sub

Collapse -

MS Excel question...

by srobertson In reply to MS Excel question...

Exactly how long does it take to get a question rated or answered.

Obviously the problem wasn't that serious to begin with.

Collapse -

MS Excel question...

by New Graduate In reply to MS Excel question...

The question was auto-closed by TechRepublic

Collapse -

MS Excel question...

by New Graduate In reply to MS Excel question...

This question was auto closed due to inactivity

Back to Web Development Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums