Concatenate a row that is selected and include blank cells

  • Thread starter Thread starter Howard
  • Start date Start date
H

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
 
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 :
 
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
Click to expand...

Thanks, isabelle, just what I was looking for.

Regards,
Howard
 
Thanks, isabelle, just what I was looking for.



Regards,

Howard
Click to expand...

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
Click to expand...

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
 
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
 
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.
Click to expand...

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
 
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.
Click to expand...



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
Click to expand...

Thanks Claus, that works very nice.

Regards,
Howard
 
Hi Howard

Am Sun, 16 Jun 2013 01:29:22 -0700 (PDT) schrieb Howard:
Thanks Claus, that works very nice.
Click to expand...

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
 
Hi Howard



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


Thanks Claus, that works very nice.
Click to expand...



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
Click to expand...


Now that's pretty snazzy!

Where do you come up with this magic?

Thanks,
Howard
 
Hi Howard,

Am Sun, 16 Jun 2013 10:32:29 -0700 (PDT) schrieb Howard:
Where do you come up with this magic?
Click to expand...

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
 
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
Click to expand...

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

Regards,
Howard
 
Back
Top