CODEWITHSUNDEEP
vbaexcel
Nandish
Nandish

Reputation: 502

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

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

Upvotes: 0

Views: 1319

Answers (1)

Automate This
Automate This

Reputation: 31364

@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

Upvotes: 1

Related Questions