Concatenate N number of columns between 2 specific column name in VBA Excel

Question

I am trying to concatenate the selected range between 2 specific columns. My first column name is "Product-name" (First column is fixed) and second specific column is not fixed. It can be 3rd, 4th, 5th or N. The name of that column is "Price". I want to concatenate all columns that fall between this 2 columns. I tried the following code.

Sub test()

    Cells(1, 1).Select
    j = 1
Do
    k = Cells(1, j).Value
    Cells(1, j).Select
    j = j + 1
Loop Until (k = "Product-name")
c1 = j
Do
    k = Cells(1, j).Value
    Cells(1, j).Select
    j = j + 1
Loop Until (k = "Price")
c2 = j - 2
If (c2 > c1) Then

  'I am doing something wrong here. Please let me know the correct syntax
  CONCATENATE(Range(Columns(c1), Columns(c2)))

End If


End Sub

I am having trouble following the purpose of your loops. The syntax for concatenation in VBA is `strFullString = strFirstHalf & strSecondHalf` [Look at this answer](http://stackoverflow.com/a/1727709/2497001) — Evan, Jun 15 '15 at 18:58
Using the above code I got the range of cells that fall between the 2 specific columns. But now I want to concatenate that Range of cells. — Nandish, Jun 15 '15 at 19:01
I should start by pointing out the small detail that you cannot concatenate ranges. What you can rather do is concatenate the value of ranges but you still need to place this either in a variable or another range. Also I don't think you can concatenate them throughout all rows this way. I would think you have to loop through each row in your 2 columns, join the values and output to a variable or range. — nbayly, Jun 15 '15 at 19:47

Automate This · Accepted Answer · 2015-06-15T19:55:40.573

1

@nbayly is correct, you can't concatenate an entire range like this, and even if you could you are not assigning the result to anything.

Here is one way to do it using a different technique. The test data looks like this:

enter image description here

Make sure you have either the column or header cell for both Product and Price set as a named range. Here is an example if your unsure what I mean:

enter image description here

Now run this code:

Sub concatTest()
    Dim wks As Worksheet
    Set wks = ActiveSheet

    Dim colStart As String, colEnd As String

    colStart = wks.Range("ProductName").Column
    colEnd = wks.Range("Price").Column

    Dim resultString As String
    Dim LastRow As Integer: LastRow = wks.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    For eachRow = 2 To LastRow
        resultString = ""

        For y = colStart To colEnd
            resultString = resultString & wks.Cells(eachRow, y)
        Next y

        Debug.Print resultString
    Next eachRow
End Sub

Results are in the immediate window, you could also put this data in some column:

enter image description here

edited Jun 15 '15 at 19:55

answered Jun 15 '15 at 19:50

Automate This

30,726
11
60
82

- Hey thanks for giving me the solution. I am trying this but getting error that "Method 'Range of object' _Worksheet failed " – Nandish Jun 15 '15 at 20:13
That error, in this case, is because the named range doesn't exist. In my code I used "ProductName" and "Price" which were both assigned to columns B & F respectively. Try setting these names and then run it again. – Automate This Jun 15 '15 at 20:24
- See my screen shot. I tried doing the same but still getting error. – Nandish Jun 15 '15 at 20:46
Okay, named range is not the same as a column name. In your screen shot, see the 'F3" in the upper left? That's where you name the range. Select column 'A', type "ProductName" in the upper left box then hit enter. Select column 'F' and type "Price" in the upper left box and then hit enter. Now it should work – Automate This Jun 15 '15 at 21:10
Awesome, glad it helped. – Automate This Jun 16 '15 at 16:32

Concatenate N number of columns between 2 specific column name in VBA Excel

1 Answers1