Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
washdcjohn
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Dave O
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Gord Dibben
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
washdcjohn
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
paste special | values should work with merged cells PastingSpecial Excel Discussion (Misc queries) 1 June 20th 05 06:51 PM
how do I paste an excel column into one cell...tia sal Excel Discussion (Misc queries) 2 June 8th 05 03:27 PM
Copy one cell to entire column Tom Excel Discussion (Misc queries) 6 April 27th 05 11:39 PM
excel - numbers as text Thuferhawat New Users to Excel 12 January 24th 05 10:29 PM
Move the last entry in a column to a different cell, when the loc. MicroSoft Excell (?) Excel Worksheet Functions 2 January 7th 05 10:29 PM


All times are GMT +1. The time now is 06:59 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"