Concatenate a row that is selected and include blank cells

[Howard]

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

 

[isabelle]

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 :

 

[Howard]

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

 

[Howard]

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

 

[Claus Busch]

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

 

[Howard]

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 theEighth 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

 

[Claus Busch]

Hi Howard,

Am Sun, 16 Jun 2013 00:36:07 -0700 (PDT) schrieb Howard:

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.

if there is a space in a selected cell it will be shown in AK15. But if
the cell is blank you have to insert a space. Try:
Sub ConcatMe2()
Dim rngC As Range

For Each rngC In Selection
If Len(rngC) = 0 Then
[AK15] = [AK15] & " "
Else
[AK15] = [AK15] & rngC.Text
End If
Next
End Sub

Regards
Claus Busch

 

[Howard]

Hi Howard,



Am Sun, 16 Jun 2013 00:36:07 -0700 (PDT) schrieb Howard:

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 Henrythe Eighth or Henry Wadsworth Longfellow or King Tut.

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

if there is a space in a selected cell it will be shown in AK15. But if

the cell is blank you have to insert a space. Try:

Sub ConcatMe2()

Dim rngC As Range



For Each rngC In Selection

If Len(rngC) = 0 Then

[AK15] = [AK15] & " "

Else

[AK15] = [AK15] & rngC.Text

End If

Next

End Sub



Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Thanks Claus, that works very nice.

Regards,
Howard

 

[Claus Busch]

Hi Howard

Am Sun, 16 Jun 2013 01:29:22 -0700 (PDT) schrieb Howard:

Thanks Claus, that works very nice.

you can shorten the IF-Statement:

Sub ConcatMe()
Dim rngC As Range

[AK15].Clear
For Each rngC In Selection
[AK15] = IIf(Len(rngC) = 0, [AK15] & " ", [AK15] & rngC.Text)
Next
End Sub


Regards
Claus Busch

 

[Howard]

Hi Howard



Am Sun, 16 Jun 2013 01:29:22 -0700 (PDT) schrieb Howard:

Thanks Claus, that works very nice.

you can shorten the IF-Statement:



Sub ConcatMe()

Dim rngC As Range



[AK15].Clear

For Each rngC In Selection

[AK15] = IIf(Len(rngC) = 0, [AK15] & " ", [AK15] & rngC.Text)

Next

End Sub





Regards

Claus Busch

Now that's pretty snazzy!

Where do you come up with this magic?

Thanks,
Howard

 

[Claus Busch]

Hi Howard,

Am Sun, 16 Jun 2013 10:32:29 -0700 (PDT) schrieb Howard:

Where do you come up with this magic?

I do many things in Excel and VBA and I read and write in many
newsgroups and web communities.
So I am always learnig about Excel


Regards
Claus Busch

 

[Howard]

Hi Howard,



Am Sun, 16 Jun 2013 10:32:29 -0700 (PDT) schrieb Howard:






I do many things in Excel and VBA and I read and write in many

newsgroups and web communities.

So I am always learnig about Excel





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Well, I sure do appreciate all the help you have given me.

Regards,
Howard