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

[Howard]

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

 

[Claus Busch]

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.

 

[Howard]

Hi Howard,



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







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