CODEWITHSUNDEEP
vbaexcel
Ikanagura
Ikanagura

Reputation: 51

VBA - Concatenate columns on the first column

I want to concatenate columns but in the first column in VBA, like that :

A         | B         | C         |
sentence1 | sentence2 | sentence3 |
sentence4 | sentence5 | sentence6 |
sentence7 | sentence8 | sentence9 |

->

A                             | B       | C      
sentence1 sentence2 sentence3 | nothing | nothing
sentence4 sentence5 sentence6 | nothing | nothing
sentence7 sentence8 sentence9 | nothing | nothing

How can I do ? Thanks in advance !

Upvotes: 0

Views: 327

Answers (3)

user3598756
user3598756

Reputation: 29421

the following does what you ask and is a little more generic in that:

  • it takes into account all cells of column "A" with some text in it

  • it extends the range whose content is to be concatenated to all consecutive non blank cells in the given row

in other words this approach doesn't suffer neither from any possible variations of columns number to consider (they can be 3, as per your example, or more or less) nor from the condition of having all rows having the same number of cells filled

Option Explicit

Sub main()
Dim cell As Range

With Worksheets("mySheet").Columns("A").SpecialCells(xlCellTypeConstants, xlTextValues)
    For Each cell In .Cells
        cell.Value = Join(Application.Transpose(Application.Transpose(Range(cell, cell.End(xlToRight)))))
        Range(cell.Offset(, 1), cell.End(xlToRight)).Clear
    Next cell
    .WrapText = False
    .EntireColumn.AutoFit
End With

End Sub

Upvotes: 1

gizlmo
gizlmo

Reputation: 1922

Dim tempval As String

Dim row As Integer, col As Integer

Application.ScreenUpdating = False

'loop through rows
For row = 1 To 3 Step 1

    'clear temp string
    tempval = ""

    'loop through columns
    For col = 1 To 3 Step 1

        'save columnvalues in temp
        tempval = tempval & Cells(row, col).Value

        'delete cell value
        Cells(row, col).Value = ""
    Next col

    'paste saved string into first cell
    Cells(row, 1).Value = tempval
Next row

Application.ScreenUpdating = True

Upvotes: 1

jsheeran
jsheeran

Reputation: 3037

Dim r As Range
For Each r In Sheet1.UsedRange
   r(1, 1).Value = r(1, 1).Value & " " & r(1, 2).Value & " " & r(1, 3).Value
   r(1, 2).Value = ""
   r(1, 3).Value = ""
Next r

Upvotes: 0

Related Questions