• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Concatenating Entire Row

I

indi visual

Member
I'm using this in my sub:


Cell = Cell.Offset(, 1).Value + Cell.Offset(, 2).Value + Cell.Offset(, 3).Value etc...


But because I have 30 offset rows to merge, I'm sure there's an easier way to achieve this.


Also, some of the rows are shorter than others, which will result in me combining empty cells uneccessarily. Is there any way to concatenate the entire row while skipping blanks?


I guess concatenating blank cells won't hurt anything?
 
I thought I found the answer here:

http://chandoo.org/wp/2010/12/07/merge-cells-without-loosing-data/


This was neat, and I think it's almost what I was looking for, but my question is still open. Still needing to concatenate without actually merging.
 
Here's a ConCat UDF you could use, along with example code on how to use it.


Code: 
Function Concat(Delim As String, MyRange As Range) As String

Dim c As Range


For Each c In MyRange

If c.Value <> "" Then

Concat = Concat & c.Value & Delim

End If

Next

Concat = Left(Concat, Len(Concat) - Len(Delim))


End Function


Sub MyMacro()

ActiveCell.Value = Concat(", ", Range("1:1"))

End Sub
 
I'm afraid I can't access shared files. =(

(not a problem on your end, I just can't access shared file websites)
 
No worries, it was just a direct link to a blank worksheet with the UDF you provided me and simple example text (with a header in A1).


Row 1: Header *not to be concatenated

Row 2: Text1 in A2 | Text2 in B2 | Text3 in C2 | Text4 in D2

Row 3: Text1 in A3 | Text2 in B3 | Text3 in C3 | Text4 in D3

Row 4: Text1 in A4 | Text2 in B4 | Text3 in C4 | Text4 in D4

Row 5: Text1 in A5 | Text2 in B5 | Text3 in C5 | Text4 in D5

Row 6: Text1 in A6 | Text2 in B6 | Text3 in C6 | Text4 in D6


Side Note: I'm trying to execute this code in the middle of another sub. I've ran some code here and there to break up A2 in pieces (like illustrated above in segments).


-and then I plan to merge it all back together in the first cell.


Ultimately,

For Each Cell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)

Concatenate each row into the first cell
 
No worries, it was simply a direct link to a blank worksheet with the UDF you provided me with a few segments:


Row 1: Header *not to be concatenated

Row 2: TextSegment1 in A2 | TextSegment2 in B2 | TextSegment3 in C2

Row 3: TextSegment1 in A3 | TextSegment2 in B3 | TextSegment3 in C3

Row 4: TextSegment1 in A4 | TextSegment2 in B4 | TextSegment3 in C4


Ultimately, my aim is like..


Dim C As Range

Dim MyRange As Range


Set MyRange = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)


For Each C In MyRange

C.Value = the Concatenation of the entire row

Next C


Final Expected Result:

Row 1: Header *not to be concatenated

Row 2: TextSegment1 TextSegment2 TextSegment3 in A2

Row 3: TextSegment1 TextSegment2 TextSegment3 in A3

Row 4: TextSegment1 TextSegment2 TextSegment3 in A4
 
With the UDF already in place, macro would be:


Sub Macro_for_indi()

Dim C As Range

Dim MyRange As Range


Set MyRange = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)


For Each C In MyRange

C.Value = Concat(" ", Range(C, C.End(xlToRight)))

Next C


End Sub
 
No luck.


I think it has something to do with MyRange.


The code looks sound, everything looks right, it's just not working for some reason.
 
Hmm, working on my end. This is what I have in a standard module:


Function Concat(Delim As String, MyRange As Range) As String

Dim c As Range


For Each c In MyRange

If c.Value <> "" Then

Concat = Concat & c.Value & Delim

End If

Next

Concat = Left(Concat, Len(Concat) - Len(Delim))


End Function


Sub Macro_for_indi()

Dim C As Range

Dim xRange As Range


Set xRange = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)


For Each C In xRange

C.Value = Concat(" ", Range(C, C.End(xlToRight)))

Next C


End Sub
 
What do you have pre-populated in your worksheet before you run the code?


I have:

Row 1: Header

Row 2: TextSegment1 in A2 | TextSegment2 in B2 | TextSegment3 in C2

Row 3: TextSegment1 in A3 | TextSegment2 in B3 | TextSegment3 in C3

Row 4: TextSegment1 in A4 | TextSegment2 in B4 | TextSegment3 in C4
 
The same. When code finishes, everything from row 2 is in A2...did you also want the code to clear out the data from columns B & C?
 
Oh, that's an easy one...it just becomes this:


For Each C In xRange

C.Value = Concat(" ", Range(C, C.End(xlToRight)))

Range(C.Offset(0,1), C.End(xlToRight))).ClearContents

Next C


Is the code generating an error, or is it just not doing what you are wanting?
 
My code was placing another value in the string I forgot about but I figured it out and fixed it.


I have no errors the code you helped me with works perfect thanks again.


I noticed it is a little slower than:

C = C.Offset(, 1).Value + C.Offset(, 2).Value + C.Offset(, 3).Value etc..


But I like the code you helped me with much better : )
 
Good to hear. Hui probably would have some idea on how to do a single read (My code reads multiple times -> slow) and use arrays, but that is one of my weaker areas. =/
 
You must log in or register to reply here.
Back
Top