Solved

Excel VBA - Concatenate cells in every other column in range

Posted on 2010-08-23
17
1,321 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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
 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

809 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