Use Vba To Join Cell Values With Either & Or Concatenate Formula

Join multiple string cells using one of 2 options, the code below shows how to choose the two, it boils down to what the user finds easier to read.

=CONCATENATE(A1,” “,B1,” “,C1) or =A1&” “&B1&” “&C1

note: Concatenate formula limitations, 255 string limit and & has none The code below uses the & option, the code points to comment out:

& Option

xRange.Formula = “=” & sArgs Concatenate option

xRange.Formula = “=CONCATENATE(“ & sArgs & “)”

& option

sArgSep = “&” Concatenate option

sArgSep = “,”

Description

Creates a formula in the active cell using the selected cells values Prompts the user for a range of cells, then prompts the user for an argument separator You can select any range of cells, continuous and or random cells, then type a separator and you’re good to go! The macro enters in a Concatenate formula to the active cell with the separator between each cell in the range you selected!

Sub JoinCellsWithVBA()
    
    '// Vars
    Dim rSelected   As Range
    Dim c           As Range
    Dim xRange      As Range
    Dim sArgs       As String
    Dim sArgSep     As String
    Dim sSeparator  As String
    Dim sTitle      As String
    Dim lTrim       As Long
    
    '// Check Cell Selected
    If TypeName(Selection) <> "Range" Then Exit Sub
    
    '// Set variables
    Set xRange = ActiveCell
    sSeparator = ""
    sTitle = "Concatenate With VBA"
    
    '// Prompt user to select cells for formula
    On Error Resume Next        'Turn off error prompts
    Set rSelected = Application.InputBox(Prompt:="Select cells To join together", Title:=sTitle, Type:=8)
    On Error GoTo 0        'Turn on error prompts
    
    '// Only run if cells were selected and cancel button was not pressed
    If Not rSelected Is Nothing Then
        
        '// Set argument separator for formula, choose for either & or Concatentate formula
        '   remember to comment out the appropriate xRange.Formula below
        '& option
        sArgSep = "&"        '& option
        
        'Concatenate option
        'sArgSep = ","  'Concatenate option
        
        '// Enter the separator between each cell value, check if user clicked cancel
        xSeparator = Application.InputBox(Prompt:="Enter separator, leave blank If none.", Title:=sTitle, Type:=2)
        
        If xSeparator = FALSE Then Exit Sub        'If User clicked cancel then exit sub
        sSeparator = xSeparator        'Set the separator as string
        
        '// Create string of cell references
        For Each c In rSelected.Cells
            sArgs = sArgs & c.Address(0, 0) & sArgSep
            If sSeparator <> "" Then
                sArgs = sArgs & Chr(34) & sSeparator & Chr(34) & sArgSep
            End If
        Next
        
        '// Trim extra argument separator and separator characters
        lTrim = IIf(sSeparator <> "", 4 + Len(sSeparator), 1)
        sArgs = Left(sArgs, Len(sArgs) - lTrim)
        
        '// Create formula to the active cell
        '& option, ensure you change sArgSep above to "&"
        xRange.Formula = "=" & sArgs
        
        'Concatenate option, ensure you change sArgSep above to ","
        'xRange.Formula = "=CONCATENATE(" & sArgs & ")"
        
    End If
End Sub