Concatenation code not working as intended

Question

I wrote a code in Excel VBA for concatenating values upto 7 columns in a row with ^ between them. I noticed that if I enter the same value in the first six columns, I get the output without the ^. This doesn't happen if i fill upto column 7 or 5. The code is as below:

Private Sub CommandButton21_Click() 
    Cells(2, 1).Select  
    Dim stri As String, eaid_1 As String, eaid_2 As String, eaid_3 As String, _
        eaid_4 As String, eaid_5 As String, eaid_6 As String, eaid_7 As String
    Do Until Selection.Value = ""     
        eaid_1 = Selection.Value     
        eaid_2 = Selection.Offset(0, 1).Value     
        eaid_3 = Selection.Offset(0, 2).Value     
        eaid_4 = Selection.Offset(0, 3).Value     
        eaid_5 = Selection.Offset(0, 4).Value     
        eaid_6 = Selection.Offset(0, 5).Value     
        eaid_7 = Selection.Offset(0, 6).Value          
        stri = eaid_1 & "^" & eaid_2 & "^" & eaid_3 & "^" & eaid_4 & "^" & eaid_5 _
                      & "^" & eaid_6 & "^" & eaid_7          
        Selection.Offset(0, 8).Value = stri     
        Selection.Offset(1, 0).Select  
    Loop  

    Cells(2, 9).Select  
    Dim x As String, y As String, z As String  
    Do Until Selection.Value = ""  
        x = Selection.Value 
        y = Right(x, 6) 
        z = Replace(y, "^", "")  
        x = Replace(x, y, z)          
        Selection.Offset(0, 0).Value = x 
        Selection.Offset(1, 0).Select  
    Loop  
End Sub

Answer 1

Private Sub CommandButton21_Click()
    Const NUM_COLS As Long = 7
    Dim c As Range, rng As Range

    Set c = Cells(2, 1)
    Do While c.Value <> ""
        Set rng = c.Resize(1, Application.CountA(c.Resize(1, NUM_COLS)))
        c.Offset(0, NUM_COLS).Value = _
               Join(Application.Transpose(Application.Transpose(rng.Value)), "^")
        Set c = c.Offset(1, 0)
    Loop
End Sub

Some explanation:

• You should avoid using Select/Activate to work with ranges, and instead use a Range variable (such as c above)
• The DoWhile...Loop starts at A2 and continues until c is blank
• The rng variable represents a Range object beginning at c and extending to the right for as many cells as there are values (to a maximum of 7 cells). The CountA worksheet function is used to count the number of values, and Resize creates a range of the required size.
• The repeated Application.Transpose creates a single-dimension array out of the 2-D array resulting for rng.Value. Don't ask me to explain exactly why that works ;-)
• Finally, Join takes the 1-D array and returns a single string with each element of the input array concatenated to the next and separated by the second argument ("^")

Answer 2

The code below will "CONCATENATE" each row (where Column A has data) and checks each row where the last Column has data, then it combines them together (adding "^" between each array elements). Currently it puts the result string in Column I, like in your post).

What is the purpose your second loop ? what your final result should look like ?

Private Sub CommandButton21_Click()

Dim Rng             As Range
Dim stri            As String
Dim eaid()          As Variant
Dim lRow            As Long
Dim i               As Long
Dim LastColumn      As Long

' start from Cell A2
lRow = 2
Do Until Range("A" & lRow).Value = ""
    ' get the last column with data in current row
    LastColumn = Cells(lRow, Columns.Count).End(xlToLeft).Column
    ReDim eaid(1 To LastColumn)

    Set Rng = Range("A" & lRow)

    ' read all Range values to one-dimension array using Transpose
    eaid = Application.Transpose(Application.Transpose(Rng.Resize(1, LastColumn).Value))

    ' read all array elements to String
    For i = LBound(eaid) To UBound(eaid)
        If i = LBound(eaid) Then
            stri = stri & eaid(i)
        Else
            stri = stri & "^" & eaid(i)
        End If
    Next i

    Rng.Offset(0, 8).Value = stri
    stri = ""
    lRow = lRow + 1
Loop

End Sub