Concat from first to the last but one in another column

Question

I want to concat all objects but last one from column A to Column B So far I come up with this idea

Set NameRange = Range("A1", Range("A1").End(xlDown))
    For Each cell In NameRange 
    cell.Offset(, 1) = cell.Text & "," 
    Next cell

and I want last cell to be in the next column which is why there is Offset(,1) but without this ","

So it looks kind of like

A  A,
B  B,
C  C,
D  D

What is the output you are expecting? – MGP Sep 17 '20 at 06:23 — MGP, Sep 17 '20 at 06:23

score 0 · Accepted Answer · answered Sep 17 '20 at 06:30

For Each doesn't remember the last cell visited (unlike a For..Next loop), but you can work around this:

Set NameRange = Range("A1", Range("A1").End(xlDown))
For Each cell In NameRange
    cell.Offset(, 1) = cell.Text & ","
    Set last = cell
Next cell
last.Offset(, 1) = Left(last.Text, Len(last.Text))

and then remove the last comma outside of the loop.

Siddharth Rout · Answer 2 · 2020-09-17T07:03:40.790

Avoid the use of xlDown. You may want to see Error in finding last used cell in Excel with VBA
Avoid the use of .Text. You may want to see What is the difference between .text, .value, and .value2?
What if last row is 1? Handle that as well.

Is this what you are trying?

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long, i As Long
    
    '~~> Change this to the relevant worksheet
    Set ws = Sheet1
    
    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        
        '~~> Check if last row is 1
        If lRow = 1 Then
            .Range("B1").Value = .Range("A1").Value
            Exit Sub
        End If
        
        For i = 1 To (lRow - 1)
            .Range("B" & i).Value = .Range("A" & i).Value & ","
        Next i
    End With
End Sub

Here is another way which doesn't use a loop

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long
    Dim sAddr As String
    
    '~~> Change this to the relevant worksheet
    Set ws = Sheet1
    
    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        
        '~~> Check if last row is 1
        If lRow = 1 Then
            Range("B1").Value = .Range("A1").Value
            Exit Sub
        End If
        
        sAddr = "A1:A" & (lRow - 1)
        
        .Range("B1:B" & (lRow - 1)).Value = _
        Evaluate("index(concatenate(" & sAddr & "," & """,""" & "),)")

        .Range("B" & lRow).Value = .Range("A" & lRow).Value
    End With
End Sub

Explanation about index(concatenate())

Concat from first to the last but one in another column

2 Answers2