Concat a three column range into one cell Then Next...
 

This works pretty good, I select the cells in three adjacent columns and run the code, all the data is in one cell, AU2 in this case.

I would like to be able to run this code down column AU, say 25 rows. (adjustable, might be 60 next column I use it in)

The three cells I want to concat code to join are AU.Offset(0, -6).Resize(1, 3).
And there are blank cells. Where blank means all three of the cells to be joined will be blank

Example data is something like this.

GRP SN 2500 STIS * 300 * PN 1

Where the * denotes the next column (if it posts ok here).

Current result for the one-cell-at-a-time process looks like this in AU2.

GRP SN 2500 STIS300PN 1

I would prefer that the spaces were remove in the process to look like this.

GRPSN2500STIS300PN1

Thanks,
Howard


Option Explicit

Sub ConcatTest()
Range("AU2").ClearContents
Dim rngC As Range

For Each rngC In Selection

If Len(rngC) = 0 Then
[AU2] = [AU2] & " "
Else

[AU2] = [AU2] & rngC.Text

End If
Next
'Range("AU2").Select
End Sub

Concat a three column range into one cell Then Next...
 

Hi Howard,

Am Wed, 16 Oct 2013 03:22:29 -0700 (PDT) schrieb Howard:

I would like to be able to run this code down column AU, say 25 rows. (adjustable, might be 60 next column I use it in)

The three cells I want to concat code to join are AU.Offset(0, -6).Resize(1, 3).
And there are blank cells. Where blank means all three of the cells to be joined will be blank

try:
Sub Test()
Dim LRow As Long
Dim i As Long
Dim myStr As String

LRow = Cells(Rows.Count, "AO").End(xlUp).Row
For i = 2 To LRow
myStr = Replace(Cells(i, "AO") & Cells(i, "AP") & _
Cells(i, "AQ"), " ", "")
Cells(i, "AU") = myStr
Next
End Sub


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

Concat a three column range into one cell Then Next...
 

On Wednesday, October 16, 2013 3:36:28 AM UTC-7, Claus Busch wrote:
Hi Howard,



Am Wed, 16 Oct 2013 03:22:29 -0700 (PDT) schrieb Howard:



I would like to be able to run this code down column AU, say 25 rows. (adjustable, might be 60 next column I use it in)



The three cells I want to concat code to join are AU.Offset(0, -6).Resize(1, 3).

And there are blank cells. Where blank means all three of the cells to be joined will be blank



try:

Sub Test()

Dim LRow As Long

Dim i As Long

Dim myStr As String



LRow = Cells(Rows.Count, "AO").End(xlUp).Row

For i = 2 To LRow

myStr = Replace(Cells(i, "AO") & Cells(i, "AP") & _

Cells(i, "AQ"), " ", "")

Cells(i, "AU") = myStr

Next

End Sub





Regards

Claus B.


Sheer magic! Works great.

As always, thank a lot.

Regards,
Howard