Concatenate a selected Range?

[Guest]

Hi All...........
With a UDF or a macro, would it be possible to CONCATENATE all the cells in
a selected Range, say A1:a7 without having to type each cell
address.........something like =SpecialCONCATENATE(A1:A7), or
=SpecialCONCATENATE(MyRange), of course those don't work, but that's the
idea.

TIA
Vaya con Dios,
Chuck, CABGx3

 

[keepITcool]

try following udf


Public Function GlueText( _
data As Variant, _
Optional delimiter As String = vbNullString) As String
'keepITcool

Dim rArea, rCell, r&, c&, s$
If TypeOf data Is Range Then
For Each rArea In data.Areas
For Each rCell In rArea.Cells
'Note: for ranges the (formatted) Text property is used
If Len(rCell) Then s = s & delimiter & rCell.Text
Next
Next
ElseIf IsArray(data) Then
On Error Resume Next
c = LBound(data, 2) + 1
On Error GoTo 0
If c > 0 Then GoTo TwoDim Else GoTo OneDim

TwoDim:
For r = LBound(data, 1) To UBound(data, 1)
For c = LBound(data, 2) To UBound(data, 2)
If Len(data(r, c)) Then s = s & delimiter & data(r, c)
Next
Next
GoTo theEND
OneDim:
For r = LBound(data) To UBound(data)
If Len(data(r)) Then s = s & delimiter & data(r)
Next
Else
s = data
End If
theEND:
GlueText = Mid(s, 1 + Len(delimiter))
End Function

 

[Guest]

That UDF or yours is only PERFECT!!!!!
Many thanks keepITcool..........

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

It's working fine, but I frequently get "Type Mismatch" error, which will
cancell out and continue to work fine............I'm using XL97.....can I do
anything to prevent the "Type mismatch" popups?

Oh yeah, thanks especially for anticipating me and including the
"delimiter" feature......that's really cool.........

Thanks,
Vaya con Dios,
Chuck, CABGx3

 

[keepITcool]

I justed tested with xl97 SR1 (nl version),
but can't reproduce your runtime error.

press DEBUG when you get popup.
then look WHERE it produces the error.

pls also tell me what is your formula
AND the content of your Data range.

 

[Guest]

welllll..........I'm embarrassed to say, I can't seem to reporduce the
problem now either...........I'm using XL97SR2, (English version) but the
pop-up was coming up regularly in that other session. The range I'm using
is named "MyRange" and it covers B1:B7 whose values I vary from numbers to
text in my tests, and it all seems to work fine.

I tested =GlueText(MyRange) and =GlueText(B1:B7) and both work. I did add
one line of code specifying the delimiter, in between the two lines
below.....I don't know if that's the place to put it or not, but it seems to
work ok.........

'keepITcool
delimiter = Range("a1").Value
Dim rArea, rCell, r&, c&, s$

So, I guess the bottom line is, "I'm a Happy Camper", and I really do
appreciate your time, and the help you have given me here.........First
Class!

Thanks again,
Vaya con Dios,
Chuck, CABGx3

 

[Guest]

Actually, it looks like he's already taken care of the delimiter in the
functions declaration (as an optional argument). You should be able to use

=Gluetext(B1:B7, A1)

without hardcoding the delimiter in the code.

 

[CLR]

That's great.........thanks for the enlightenment JMB!
I don't have Excel up and running here at home tonight, but I'll for sure
try it tomorrow.....

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

Follow-on question for this great function. How could this be turned into a
Macro, such that this functionality occurs in the following manner ...?

Select the cells of text to be concatenated
Hit some quick key sequence (e.g., Ctrl+shift+C)
The concatenated string replaces the first cell's contents and all the
subsequent text cells used are cleared.

The only reason I ask is that I have a spreadsheet where the number of cells
to concatenate varies from row to row, so I cannot easily leverage the
function b/c it wouldn't "drag" very well. I was trying to create
functionality that would allow me to select the text cells in each row. A
little more manual, but probably easier than using the function for a purpose
not intended.

Any thoughts?

Thanks in advance,
Keith

 

[Guest]

Hi. This was an awesome function, but I have a question that puts a spin on
it a bit. I can't use it in function form, b/c row by row, I have variable
number of cells with text to be concatenated. As a result, the function
doesn't "drag" well, but otherwise performs the exact function I need. I was
toying with making it a macro, but I fear this is well beyond my novice VBA
skills.

Could you point me to how to "Macro-ize" this, where the user 1) selects the
text cells to be concatenated 2) hits some quick key sequence, e.g.,
ctrl-shift-C, 3) the cells are concatenated and the result is returned to the
first cell 4) the piecemeal text cells are deleted and all contents slide to
the left to be adjacent to the cell where results were returned.

This process would be a little more manual, b/c I'd have to go line by line,
but my data gives me no choice. Am I missing some simple use of your function
that would make this easy?

Any assistance would be greatly appreciated.

Thanks and regards,
Keith

 

[Guest]

Apologize for the double-post earlier - my browser froze, so thought I lost
the post.

I have managed to solve most of my problem by thoroughly and shamelessly
borrowing from other posts. I have the following. It takes the selected range
of cells (intended to be on the same row) concatenates them, inserts a cell
after them and puts the result in that extra cell. How would I modify this if
I wanted to delete the "source cells?"

Sub ConcatHorizText()
Dim Col As Range
Dim Cell As Range
Dim FirstColumn
Dim LastColumn
Dim ColumnCount
Dim Result As String

With Selection
FirstColumn = .Item(1).Column
ColumnCount = .Columns.Count
LastColumn = FirstColumn + ColumnCount - 1
For Each Col In .Columns
For Each Cell In Col.Cells
Result = Result & Cell.Value
Next Cell
If Col.Column < .Columns(.Columns.Count).Column Then _
Result = Result & " "
Next Col
Cells(.Rows(.Rows.Count).Row, _
.Columns(LastColumn).Column + 1).Insert (xlShiftToRight)
Cells(.Rows(.Rows.Count).Row, _
.Columns(LastColumn).Column + 1).Value = Result
End With
End Sub

Any other tips would be useful as I am a terribly novice VBA coder

Thanks,
Keith

 

[Guest]

My understanding is you want to select a row of cells, concatenate them and
have the result appear in the first cell of the selected range and have the
rest of the selected cells cleared. Maybe something like this will give you
some ideas (just be sure to back up your work).


Sub ConcatHorizText()
Const Delimiter As String = " "
Dim Result As String
Dim y As Range

For Each y In Selection.Cells
If y.Value <> "" Then _
Result = Result & Delimiter & y.Value
Next y

Selection.Clear
Selection.Cells(1, 1).Value = Trim(Result)

End Sub


If you have a two dimensional area(s) selected and wanted the cells
concatenated in a specific order (concatenated by column instead of by row -
which seems to be Excel's default on my machine) you would have to modify it.

Sub ConcatHorizText()
Const Delimiter As String = " "
Dim Result As String
Dim y As Range

For Each x In Selection.Columns
For Each y In x.Cells
If y.Value <> "" Then _
Result = Result & Delimiter & y.Value
Next y
Next x

Selection.Clear
Selection.Cells(1, 1).Value = Trim(Result)

End Sub


Hope some of it's helpful.

 

[CLR]

Great JMB!!......
And both work fine on a non-contiguious selection of cells, putting the
concatination in order by the order of selection......then clearing all the
original cells........'way cool......another one for my secret goodie-stash.

Thanks,
Vaya con Dios,
Chuck, CABGx3

 

[Guest]

I wasn't sure how it would handle the non-contiguous ranges until writing it
and playing with it. I figured I'd have to loop through each area. As is
usual, there was some luck involved (well - there's always some luck
involved, but it's usually the bad kind)! : )

 

[Guest]

Thanks so much! Much tighter code, and I learned some VBA along the way!
Appreciate the response.

Keith

 

[Guest]

Try this function


Function AddText(CellsToAdd, TextSeperator)
y = CellsToAdd.Cells.Count
i = 1
For i = 1 To y
z = CellsToAdd(i)
k = k & TextSeperator & z
Next i
AddText = k
End Function