CODEWITHSUNDEEP
excelvba
Maykid
Maykid

Reputation: 517

Concatenate with VBA Excel

So what I'm trying to do is, I want to be able to press a button in Excel to concatenate multiple columns. I've seen it done before but not sure how to go about creating it. I have information starting in B2:B100, to J2:J100 and need to have everything together in column A separated by a "_" corresponding with each individual line from A2:A100.

The data inbetween B2:J100 change frequently and that's why I would like to have it as a button instead of just Concatenate function and dragging all the way down. Just looking for something that looks cleaner.

Any help would be great!

-Maykid

Upvotes: 0

Views: 10664

Answers (3)

Neil B
Neil B

Reputation: 2224

If you want to concatenate ignoring blank cells you could do something like this.

Dim temp As String
For Row = 2 To 100
    temp = ""
    For col = 2 To 10
        If Cells(Row, col) <> "" Then
            If temp <> "" Then temp = temp & "_"              
            temp = temp & Cells(Row, col)
        End If
    Next col

    Cells(Row, 1) = temp
Next Row

Update2: If you want to totally ignore any rows containing blank fields use an else statement.

Dim temp As String
For Row = 2 To 100
    temp = ""
    For col = 2 To 4
        If Cells(Row, col) <> "" Then
            If temp <> "" Then temp = temp & "_"
            temp = temp & Cells(Row, col)
        Else
            temp = ""
            Exit For
        End If
    Next col

    Cells(Row, 1) = temp
Next Row

Upvotes: 1

DisplayName
DisplayName

Reputation: 13386

Sub Test()
    Dim iRow As Long

    For iRow = 2 To 100 
        With Range("B:J").Rows(iRow)
            If WorksheetFunction.CountBlank(.Cells)>0 Then .SpecialCells(xlCellTypeBlanks).Value = "|"
            Cells(iRow, 1).Value = Replace(Join(Application.Transpose(Application.Transpose(.Value)),"_"), "_|_","")
            .Replace what:="|", Replacement:= "", LookAt:=xlWhole
        End With
    Next
End Sub

Upvotes: 0

dwirony
dwirony

Reputation: 5450

But if you really need a VBA solution, that would be this:

Sub Test()

For i = 2 To 100
    Range("A" & i).Value = Range("B" & i).Value & "_" & Range("J" & i).Value
Next i

End Sub

Upvotes: 2

Related Questions