VBA Excel: Concatenate columns

Hello experts,

I have the following procedure:

Sub Concat() Dim lRow As Long
     lRow = Range("F" & Rows.count).End(xlUp).Row
 
     For i = 2 To lRow 
         Cells(i, 10) = Cells(i, 6) &  Cells(i, 8) 
      Next i 
End Sub

Function ColumnLetter(ColumnNumber As Long) As String
    Dim n As Long
    Dim c As Byte
    Dim s As String

    n = ColumnNumber
    Do
        c = ((n - 1) Mod 26)
        s = Chr(c + 65) & s
        n = (n - c) \ 26
    Loop While n > 0
    ColumnLetter = s
End Function

Open in new window


I would like to re-adapt it by adding the following requirement:

1-Add an inputbox asking "In which column letter do you want to loop the concatenation?""F" in my procedure case. Checking if the value reported is not equal to a letter stop the program + msgbox the column reported is not valid.
2-Add an inputbox asking "Report your destination column in which you want to report your concatenation?" in my code case it is equal to "10" however it should be great to be able to report the column Letter. Checking if the value is not equal to a letter stop the program + msgbox the column reported is not valid.
3-Add an inputbox asking "Report the Columns letters that you want to concatenate"
Here is the tricky thing: In my procedure I am just able to concatenate values of two columns it would be great to concatenate more that than 2: Ex: I report A:B:C:D (I don't know if the ":" is the best to report the concatenation), I should have the concatenation of the 4 columns in Destination column. If possible I should be able to report as match as concatenation columns as I want. If this is not possible I would like to have an option to concatenate 2, 3 up to 4 columns.

Thank you again for your help.
LVL 1
LD16Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ThomasMcA2Commented:
It looks like you are making the process more complicated than it needs to be. The easiest way to concatenate columns is with a formula, and VBA can automate that.

Use =Concatenate(B1, C1) to concatenate cells B1 and C1.

To assign that formula in VBA, use this syntax:
.Range("A1").Formula ="=Concatenate(B1, C1)"

Open in new window


To concatenate more than 2 cells, just add them to the list: =Concatenate(B1, C1, D1, E1)
0
byundtCommented:
Here is a macro that will ask the user where the concatenation formula should go, and which cells to be concatenated.  User may choose as many cells to be concatenated as desired, control clicking when cells aren't contiguous. As written, the cells being concatenated will be on same worksheet and row as destination cell. Macro will put the concatenation formula in all cells in used range starting with row 2.
Sub Concatenator()
'Asks the user where to put a concatenation formula and which columns to be concatenated. Then does it from row 2 through end of data.
Dim ar As Range, cel As Range, col As Range, rgConcat As Range, rgDest As Range
Dim i As Long, n As Long
Dim frmla As String
n = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1 'Row number of last row of data
On Error Resume Next
Set rgDest = Application.InputBox("Select any cell in column to received concatenation results", Default:=ActiveCell.Address, Type:=8)
If Not rgDest Is Nothing Then _
    Set rgConcat = Application.InputBox("Select any cell in columns to be concatenated", "Control click if cells not contiguous", Type:=8)
On Error GoTo 0
If rgConcat Is Nothing Then Exit Sub

Set rgDest = rgDest.EntireColumn.Cells(2, 1).Resize(n - 1)
For Each ar In rgConcat.Areas
    For Each col In ar.Columns
        Set cel = col.EntireColumn.Cells(2, 1)
        frmla = frmla & "," & cel.Address(False, False, xlR1C1, , rgDest.Cells(1, 1))
    Next
Next
frmla = "=CONCATENATE(" & Mid(frmla, 2) & ")"
rgDest.FormulaR1C1 = frmla
rgDest.Calculate        'I found this step necessary to force formula to use A1 addressing
'rgDest.Formula = rgDest.Value      'Replace concatenation formulas with values returned
End Sub

Open in new window

ConcatenatorQ28735428.xlsm
0
byundtCommented:
I thought you might want a separator between each cell's value in the concatenation, so I revised the code to permit that option. The user will be asked to specify a separator string, which could be nothing, or it could be one or more characters.
Sub Concatenator()
'Asks the user where to put a concatenation formula and which columns to be concatenated. Then does it from row 2 through end of data.
Dim ar As Range, cel As Range, col As Range, rgConcat As Range, rgDest As Range
Dim i As Long, n As Long
Dim frmla As String, sep As String, Separator As String
n = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1 'Row number of last row of data
On Error Resume Next
Set rgDest = Application.InputBox("Select any cell in column to received concatenation results", Default:=ActiveCell.Address, Type:=8)
If Not rgDest Is Nothing Then _
    Set rgConcat = Application.InputBox("Select any cell in columns to be concatenated", "Control click if cells not contiguous", Type:=8)
On Error GoTo 0
If rgConcat Is Nothing Then Exit Sub

Separator = InputBox("What separator characters (if any) do you want between each cell's value?")
sep = IIf(Len(Separator) = 0, ",", ",""" & Separator & """,")
Set rgDest = rgDest.EntireColumn.Cells(2, 1).Resize(n - 1)
For Each ar In rgConcat.Areas
    For Each col In ar.Columns
        Set cel = col.EntireColumn.Cells(2, 1)
        frmla = frmla & sep & cel.Address(False, False, xlR1C1, , rgDest.Cells(1, 1))
    Next
Next
frmla = "=CONCATENATE(" & Mid(frmla, IIf(Len(Separator) = 0, 2, Len(Separator) + 4)) & ")"
rgDest.FormulaR1C1 = frmla
rgDest.Calculate        'I found this step necessary to force formula to use A1 addressing
'rgDest.Formula = rgDest.Value      'Replace concatenation formulas with values returned
End Sub

Open in new window

ConcatenatorQ28735428.xlsm
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

aikimarkCommented:
use my Better Concatenate function: http://rdsrc.us/GOGGgl
0
LD16Author Commented:
Thank you both for this proposal:

@byundt:

I tested your last code and it works perfectly thank you for the separator inputbox added in the revised code.

I have some some remarks:

1-Is there a way to add an additional inputbox with the following information:
("From which row N do you want to begin your concatenation?").
If user report 3 then the concatenation needs to be done as of row 3 through end of data.
If user don't report a number or an invalid value exit the program with an msgbox: "Invalid data reported in your previous input box.

It would be great to have by default row2 in this new inputbox. However the user should be able to report another row N if necessary.

2-Concerning the rgDest it would be great to make a clear before the concatenation process in order to be sure that this rgDest will contain just result of concatenation process and not another data entered manually before launching the concatenation process.  

Thank you again for your help.
0
LD16Author Commented:
@aikimark: thank you for sharing your concatenation function I am going to read the article.
0
byundtCommented:
Per your requests, I revised the code as shown below. Rather than add an input box to ask the user to specify the first row to receive results, I decided to ask the user to pick the first cell in the column to receive those results. The input box defaults to row 2 in the same column as the active cell. I also clarified the wording in that input box so it was clear any pre-existing data would be wiped out.
Sub Concatenator()
'Asks the user where to put a concatenation formula and which columns to be concatenated. Then does it from row 2 through end of data.
Dim ar As Range, cel As Range, col As Range, rgConcat As Range, rgDest As Range
Dim i As Long, n As Long
Dim frmla As String, sep As String, Separator As String
n = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1 'Row number of last row of data
On Error Resume Next
Set rgDest = Application.InputBox("Select the first cell in column to received concatenation results", _
        Default:=ActiveCell.EntireColumn.Cells(2, 1).Address, Type:=8)
If Not rgDest Is Nothing Then _
    Set rgConcat = Application.InputBox( _
        "Select any cell in columns to be concatenated." & vbLf & "Note: any pre-existing data in this column will be replaced by concatenation", _
        "Control click if cells not contiguous", Type:=8)
On Error GoTo 0
If rgConcat Is Nothing Then Exit Sub

Separator = InputBox("What separator characters (if any) do you want between each cell's value?")
sep = IIf(Len(Separator) = 0, ",", ",""" & Separator & """,")
Set rgDest = Range(rgDest.Cells(1, 1), rgDest.EntireColumn.Cells(n, 1))
For Each ar In rgConcat.Areas
    For Each col In ar.Columns
        Set cel = col.EntireColumn.Cells(rgDest.Row, 1)
        frmla = frmla & sep & cel.Address(False, False, xlR1C1, , rgDest.Cells(1, 1))
    Next
Next
frmla = "=CONCATENATE(" & Mid(frmla, IIf(Len(Separator) = 0, 2, Len(Separator) + 4)) & ")"
rgDest.FormulaR1C1 = frmla
rgDest.Calculate        'I found this step necessary to force formula to use A1 addressing
'rgDest.Formula = rgDest.Value      'Replace concatenation formulas with values returned
End Sub

Open in new window

ConcatenatorQ28735428.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LD16Author Commented:
Working perfectly. Thank you again for your help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.