Can this neat little snippet be altered to work on a row of selected cells AND include the blanks?

Works fine as is with =ConcatMe("A1:Z1") but omits any blanks.

I may want only A1:G1 including the blank cells if any. =ConcatMe('Selection')

Thanks,
Howard

Option Explicit

Function ConcatMe(Rng As Range) As String

Dim cl As Range

ConcatMe = ""

For Each cl In Rng
ConcatMe = ConcatMe & cl.Text
Next cl

End Function

hi Howard,

Function ConcatMe(Rng As Range) As String
Dim cl As Range
ConcatMe = ""
For Each cl In Rng
If cl = "" Then
ConcatMe = ConcatMe & " "
Else
ConcatMe = ConcatMe & cl.Text
End If
Next cl
End Function

isabelle

Le 2013-06-15 20:53, Howard a écrit :
Can this neat little snippet be altered to work on a row of selected cells AND include the blanks?

Works fine as is with =ConcatMe("A1:Z1") but omits any blanks.

I may want only A1:G1 including the blank cells if any. =ConcatMe('Selection')

Thanks,
Howard

Option Explicit

Function ConcatMe(Rng As Range) As String

Dim cl As Range

ConcatMe = ""

For Each cl In Rng
ConcatMe = ConcatMe & cl.Text
Next cl

End Function

On Saturday, June 15, 2013 6:08:26 PM UTC-7, isabelle wrote:
hi Howard,



Function ConcatMe(Rng As Range) As String

Dim cl As Range

ConcatMe = ""

For Each cl In Rng

If cl = "" Then

ConcatMe = ConcatMe & " "

Else

ConcatMe = ConcatMe & cl.Text

End If

Next cl

End Function



isabelle

Thanks, isabelle, just what I was looking for.

Regards,
Howard

On Saturday, June 15, 2013 8:54:26 PM UTC-7, Howard wrote:
On Saturday, June 15, 2013 6:08:26 PM UTC-7, isabelle wrote:

hi Howard,







Function ConcatMe(Rng As Range) As String



Dim cl As Range



ConcatMe = ""



For Each cl In Rng



If cl = "" Then



ConcatMe = ConcatMe & " "



Else



ConcatMe = ConcatMe & cl.Text



End If



Next cl



End Function







isabelle



Thanks, isabelle, just what I was looking for.



Regards,

Howard

Well, drat! I should have seen this before I posted.
I want to select a variable range of cells in a row and while selected run this little macro.

Sub ConCatAK_15()
Range("AK15").Formula = "=ConcatMe(A1:H1)"
End Sub

So AK15 would then be =ConcatMe(what I selected)

I don't know what to Dim and set as the selection...?
Dim xxx as Selection and then Range("AK15").Formula = "=ConcatMe(xxx)"

Howard

Hi Howard,

Am Sat, 15 Jun 2013 21:27:30 -0700 (PDT) schrieb Howard:

Well, drat! I should have seen this before I posted.
I want to select a variable range of cells in a row and while selected run this little macro.

Sub ConCatAK_15()
Range("AK15").Formula = "=ConcatMe(A1:H1)"
End Sub

if you want to concatenate A and activecell.row: H and Activecell.row
try:
Sub ConcatMe()
Dim rngC As Range
With ActiveCell
For Each rngC In Range(Cells(.Row, 1), Cells(.Row, 8))
[AK15] = [AK15] & rngC.Text
Next
End With
End Sub

if you only want to concatenate your selected cells try:

Sub ConcatMe2()
Dim rngC As Range

For Each rngC In Selection
[AK15] = [AK15] & rngC.Text
Next
End Sub


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

This second one is what I had in mind. I struggled with a macro that did similar to what you posted EXCEPT it returned everything in reverse and would not include the blank cells. What I will use this for is single common words where there will be no blanks, but also on proper names like Henry the Eighth or Henry Wadsworth Longfellow or King Tut.

K i n g T u t so will need to include the blank/space cell.

Howard

if you only want to concatenate your selected cells try:

Sub ConcatMe2()
Dim rngC As Range

For Each rngC In Selection
[AK15] = [AK15] & rngC.Text
Next
End Sub