Code to Concat a row in loop

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

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

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



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






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

That cuts right to the chase, thanks, Claus.

Regards,
Howard
 
Back
Top