Code to Concat a row in loop
 

Want to loop through A2:A10 and concat the first occupied cell to the last in each row into column A.

Code as is, if activecell is NOT in the A2:A10 range before running the code, code goes into endless loop. I am assuming that has to do with setting Lfcel and Rtcel using "Activecell.Row,".

If activecell is in A2:A10 then, then all cells are filled with the concat of that row only.

The "If i.Value 4 Then" is commented out while trying to get code to run correctly. Would like to be able to exclude certain values if the need arises. That could present major hassle as some of the values could be less than 4 and others could be regular text (A, b, Cx) etc. along with values greater than the 4 mentioned. (4 is arbitrary by the way, could be 6, whatever)

Thanks,
Howard

Option Explicit
Sub ConcatRow()

Dim c As Range
Dim i As Range
Dim Lfcel As Range
Dim Rtcel As Range

Set Lfcel = Cells(ActiveCell.Row, 2)
Set Rtcel = Cells(ActiveCell.Row, Columns.Count)

If IsEmpty(Lfcel) Then Set Lfcel = Lfcel.End(xlToRight)
If IsEmpty(Rtcel) Then Set Rtcel = Rtcel.End(xlToLeft)

For Each c In Range("A2:A10")
For Each i In Range(Lfcel, Rtcel)
'If i.Value 4 Then
c = c.Value & i.Value
'End If
Next ' i
Next 'c

End Sub

Code to Concat a row in loop
 

Hi Howard,

Am Tue, 9 Jul 2013 07:18:22 -0700 (PDT) schrieb Howard:

Want to loop through A2:A10 and concat the first occupied cell to the last in each row into column A.

Code as is, if activecell is NOT in the A2:A10 range before running the code, code goes into endless loop. I am assuming that has to do with setting Lfcel and Rtcel using "Activecell.Row,".

If activecell is in A2:A10 then, then all cells are filled with the concat of that row only.

The "If i.Value 4 Then" is commented out while trying to get code to run correctly. Would like to be able to exclude certain values if the need arises. That could present major hassle as some of the values could be less than 4 and others could be regular text (A, b, Cx) etc. along with values greater than the 4 mentioned. (4 is arbitrary by the way, could be 6, whatever)

try:

Sub ConcatRow()
Dim c As Range
Dim i As Range
Dim Rtcel As Range

For Each c In Range("A2:A10")
Set Rtcel = Cells(c.Row, Columns.Count).End(xlToLeft)
For Each i In Range(c.Offset(, 1), Rtcel)
c = c & i
Next
Next
End Sub


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

Code to Concat a row in loop
 

On Tuesday, July 9, 2013 7:51:46 AM UTC-7, Claus Busch wrote:
Hi Howard,



Am Tue, 9 Jul 2013 07:18:22 -0700 (PDT) schrieb Howard:



Want to loop through A2:A10 and concat the first occupied cell to the last in each row into column A.



Code as is, if activecell is NOT in the A2:A10 range before running the code, code goes into endless loop. I am assuming that has to do with setting Lfcel and Rtcel using "Activecell.Row,".



If activecell is in A2:A10 then, then all cells are filled with the concat of that row only.



The "If i.Value 4 Then" is commented out while trying to get code to run correctly. Would like to be able to exclude certain values if the need arises. That could present major hassle as some of the values could be less than 4 and others could be regular text (A, b, Cx) etc. along with values greater than the 4 mentioned. (4 is arbitrary by the way, could be 6, whatever)



try:



Sub ConcatRow()

Dim c As Range

Dim i As Range

Dim Rtcel As Range



For Each c In Range("A2:A10")

Set Rtcel = Cells(c.Row, Columns.Count).End(xlToLeft)

For Each i In Range(c.Offset(, 1), Rtcel)

c = c & i

Next

Next

End Sub





Regards

Claus B.

That cuts right to the chase, thanks, Claus.

Regards,
Howard