Solved

Excel VBA - Concatenate cells in every other column in range

Posted on 2010-08-23
17
1,278 Views
Last Modified: 2012-05-10
The attached file has some columns where I would like to join all the text columns (every second column starting with B) and put the results in a column on Sheet1, for example.
Hard-coded with a formula, it would be pretty simple  =concatenate(b2, " | ", d2, " | ", f2, " | ", h2) with the pipe-symbol " | " dividing each text field.  Or it could just be =b2 & " | " & d2 ... etc.  Then paste the formula down the page.

I cannot figure out how to do this in VBA though.
I think you have to use a For ... Each and count the columns across to End(xlToLeft) then Step 2 for every other column.
Then ...?  each cell is added to the next one until the end of the range. ???
For whatever reason the syntax for the Range and Cells collection (and the logic of counting across and then referencing the variables) does not make sense or work for me.
Thanks for your help!
sampleText-concatenate.xls
0
Comment
Question by:billb1057
  • 7
  • 7
  • 3
17 Comments
 
LVL 24

Accepted Solution

by:
broomee9 earned 300 total points
ID: 33504853
Try this:

Sub ConcatenateText()
    
    Dim lastRow As Long
    Dim lastCol As Long
    Dim tempLastRow 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
    
    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, 1).Value = Right(conValue, Len(conValue) - 3)
        conValue = ""
    Next i
    
    MsgBox "Done!"
    
End Sub

Open in new window

sampleText-concatenate.xls
0
 
LVL 24

Expert Comment

by:broomee9
ID: 33504879
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
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33504892
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

Open in new window

0
 
LVL 2

Author Comment

by:billb1057
ID: 33504895
Works perfectly.   Thanks!!!
(Now I will try to figure out how you did it).  :-)
0
 
LVL 13

Assisted Solution

by:MWGainesJR
MWGainesJR earned 50 total points
ID: 33504903
opps, one change
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

    txt = ""

Next  

                  

End Sub

Open in new window

0
 
LVL 24

Expert Comment

by:broomee9
ID: 33504906
Great!

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

Author Comment

by:billb1057
ID: 33504920
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?
0
 
LVL 2

Author Comment

by:billb1057
ID: 33504951
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?
 
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 2

Author Comment

by:billb1057
ID: 33504962
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?
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33504983
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.....
0
 
LVL 2

Author Comment

by:billb1057
ID: 33505005
That's a great idea - thanks.
0
 
LVL 24

Expert Comment

by:broomee9
ID: 33505016
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.
0
 
LVL 2

Author Comment

by:billb1057
ID: 33505021
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?
0
 
LVL 24

Expert Comment

by:broomee9
ID: 33505092
>>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.


0
 
LVL 24

Expert Comment

by:broomee9
ID: 33505131
>>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

Open in new window

0
 
LVL 2

Author Comment

by:billb1057
ID: 33505204
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.
0
 
LVL 24

Expert Comment

by:broomee9
ID: 33505239
Thanks, I'm happy to help :-)
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now