Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
concatenate cells from a column and paste to a cell
I'm trying to write a macro that will loop through a column and copy the data
to a single concatenated string which will be pasted to another cell. The macro should step through the column of data until it reaches the end of the data set or an empty cell. I could name a range if needed. Any help getting me started???? The concatenate( ) function won't work with ranges..., so I assume I'll have to write a macro to loop. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
concatenate cells from a column and paste to a cell
This will do it, but you may not be happy with the results since the
resulting text string may be longer than Excel allows. This code runs from the cell pointer's current location until a blank cell is encountered, then enters the string into cell A1. You can change the target location by changing the cell reference in the line below that says range("a1").value = bdstring sub Conc_This_Column dim BDString as text do until activecell.value = "" bdstring = bdstring & activecell.value activecell.offset(1,0).select loop range("a1").value = bdstring end sub |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
concatenate cells from a column and paste to a cell
Sub ConCat_Cells()
Dim x As Range Dim y As Range Dim z As Range Dim w As String Dim sbuf As String On Error GoTo endit w = InputBox("Enter the Type of De-limiter Desired") Set z = Application.InputBox("Select Destination Cell", _ "Destination Cell", , , , , , 8) Set x = Range(ActiveCell, ActiveCell.End(xlDown)) For Each y In x If Len(y.Text) 0 Then sbuf = sbuf & y.Text & w Next z = Left(sbuf, Len(sbuf) - 1) Exit Sub endit: MsgBox "Nothing Selected. Please try again." End Sub Gord Dibben Excel MVP On Tue, 15 Nov 2005 09:02:05 -0800, "washdcjohn" wrote: I'm trying to write a macro that will loop through a column and copy the data to a single concatenated string which will be pasted to another cell. The macro should step through the column of data until it reaches the end of the data set or an empty cell. I could name a range if needed. Any help getting me started???? The concatenate( ) function won't work with ranges..., so I assume I'll have to write a macro to loop. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
concatenate cells from a column and paste to a cell
Both worked! thanks.
"washdcjohn" wrote: I'm trying to write a macro that will loop through a column and copy the data to a single concatenated string which will be pasted to another cell. The macro should step through the column of data until it reaches the end of the data set or an empty cell. I could name a range if needed. Any help getting me started???? The concatenate( ) function won't work with ranges..., so I assume I'll have to write a macro to loop. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
paste special | values should work with merged cells | Excel Discussion (Misc queries) | |||
how do I paste an excel column into one cell...tia sal | Excel Discussion (Misc queries) | |||
Copy one cell to entire column | Excel Discussion (Misc queries) | |||
excel - numbers as text | New Users to Excel | |||
Move the last entry in a column to a different cell, when the loc. | Excel Worksheet Functions |