Excel VBA - Concatenate cells in every other column in range

If your sheet names ever change, then just change these two lines:

    destSheet = "Sheet1"
    conSheet = "surveyText"

destSheet is the sheet you want the concatenated values to be entered one
conSheet is the sheet where the values to concatenate are located

try this:

Sub append_text()
Dim txt As String

For i = 2 To Cells(1, 65536).End(xlUp).Row
    For j = 2 To Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column Step 2
        txt = txt & Cells(i, j).Value & "|"
    Next
    Sheet1.Range("A" & i).Value = txt
Next
                
End Sub

Select allOpen in new window

Works perfectly.   Thanks!!!
(Now I will try to figure out how you did it).  :-)

Great!

>>Now I will try to figure out how you did it
If you have any questions, let me know.

Full points to broomee.
50 bonus points (each MW and broomee) on a follow-up request ... could you give a brief explanation on your code?

For example:
What is this doing?
Sheets(conSheet).Cells(i, p).Value
Getting the value for each cell in the looped range?
and this
Sheets(destSheet).Range("A" & Rows.Count).End(xlUp).Row + 1

Going down one row each?
 

MW what does this line do?
For j = 2 To Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column Step 2
It searches back two to find the asterisk?

It searches backward to find the last column with data......That way you don't have a for loop going through every column in the spreadsheet, only the columns being used.....

That's a great idea - thanks.

These are variables to hold the sheet names, so you don't have to change within the code, just at the top:

    destSheet = "Sheet1"
    conSheet = "surveyText"

These will get the last row and column based on the last value entered in column A and row 1:
    lastRow = Sheets(conSheet).Range("A" & Rows.Count).End(xlUp).Row
    lastCol = Sheets(conSheet).Range(Columns.Count & ":1").End(xlToRight).Column


This outter loop will loop through all the rows in the conSheet (surveyText):
    For i = 2 To lastRow

    Next i

This inner loop will loop through all of the columns on the row it's currently looping through (based on the value of "i" in the outter loop) and it will start at column B (p = 2) and skip every other column by using "Step 2":
        For p = 2 To lastCol Step 2

        Next

This holds the value of each cell being looped through.  You can set the variable equal to itself, so whatever it currently holds it will still hold, and then append something to the end of it with the ampersands:
        conValue = conValue & " | " & Sheets(conSheet).Cells(i, p).Value

This will get the lastrow + 1 (so you don't overwrite the lastrow) on the destSheet (sheet1)
        tempLastRow = Sheets(destSheet).Range("A" & Rows.Count).End(xlUp).Row + 1

This will place the value on conValue into the destSheet and it will remove the first " | " set so your concatenated data will start with whatever you have listed in the conSheet
        Sheets(destSheet).Cells(tempLastRow, 1).Value = Right(conValue, Len(conValue) - 3)

This will clear out the value of the conValue variable.  This is important, because above in the code, you're adding the value of conValue to itself to retain the values in each of the columns for each row
        conValue = ""

Notifies you that the macro is done
        MsgBox "Done!"


Not a brief explanation, but this should be worthwhile for anyone new coming across this that might not understand it that well.

broomee -- one final question ..
How would I change the column where the results are placed?  For example, the first column after the last used column?

>>Sheets(destSheet).Range("A" & Rows.Count).End(xlUp).Row + 1
This is the equivalent of going to the last row in column A (will work with 2003 going to row 65536 and will work with Excel 2007/2010 going to row 1million +) and pressing Ctrl + Up Arrow.  It will reach the first row that has a value in it and set that row value to a variable.  The + 1 at the end, will add 1 to whatever value it came up with.  So if your data ends on row 26, the + 1 will start on row 27 so it doesn't overwrite what's on row 26.

>>Sheets(conSheet).Cells(i, p).Value
This will take the value of the cell on the conSheet with the row of i and the column of p.  So on the first iteration it will take the value of row 2 and column 2.  On the second iteration it will take row 2 and column 4, then row 2 column 6, etc.  all the way to the value of the lastCol variable.  Then it will start on row 3, column 2, and move to row 3 column 4, then row 3 column 6, etc. all the way through your data.

>>How would I change the column where the results are placed?  For example, the first column after the last used column?

You would change this 1 (which signifies column A) to whatever value you want.  2 for B, 3 for C, 4 for D etc.
Sheets(destSheet).Cells(tempLastRow, 1).Value = Right(conValue, Len(conValue) - 3)

If you want it to be dynamic, then you would set it to a variable, like so:
Sheets(destSheet).Cells(tempLastRow, tempLastCol).Value = Right(conValue, Len(conValue) - 3)

Of course, you would have to initialize the variable.  See amended code below.

Sub ConcatenateText()
    
    Dim lastRow As Long
    Dim lastCol As Long
    Dim tempLastRow As Long
    Dim tempLastCol As Long
    Dim i As Long 'Increment Rows
    Dim p As Long 'Increment Columns
    Dim conValue As String
    Dim conSheet As String 'Concatenate Sheet Name
    Dim destSheet As String 'Destination Sheet Name
    
    destSheet = "Sheet1"
    conSheet = "surveyText"

    lastRow = Sheets(conSheet).Range("A" & Rows.Count).End(xlUp).Row
    lastCol = Sheets(conSheet).Range(Columns.Count & ":1").End(xlToRight).Column
    tempLastCol = Sheets(destSheet).Range(Columns.Count & ":1").End(xlToRight).Column + 1
    
    For i = 2 To lastRow
        For p = 2 To lastCol Step 2
            conValue = conValue & " | " & Sheets(conSheet).Cells(i, p).Value
        Next
        tempLastRow = Sheets(destSheet).Range("A" & Rows.Count).End(xlUp).Row + 1
        Sheets(destSheet).Cells(tempLastRow, tempLastCol).Value = Right(conValue, Len(conValue) - 3)
        conValue = ""
    Next i
    
    MsgBox "Done!"
    
End Sub

Select allOpen in new window

Thanks for such a great explanation on all of the code!  I think this is the first time I'm starting to understand what's going on, rather than just copy and pasting the results.  :-)
I really appreciate your time on this, broomee.  It's a great solution.  I added bonus points as mentioned above.

Thanks, I'm happy to help :-)