VBA To Concatenate certain columns

[titoexcel]

Hi - can someone help me write VBA to concatenate columns "A". "F" and "I" and put the results in column "E" until it runs out and briefly explain how the code is structured.

ex.

Column A | Column E | Column F | Column I |

123 123x873 x 873

The VBA should do it until the last row of column A is empty. Then i would just put a command button and assign a macro to it or if possible that it runs everytime cell is updated?

thanks for the help!

 

[blaow]

You could skip VBA and use the standard excel command:

=CONCATENATE(A2, F2, I2)

 

[titoexcel]

I did this piece but everytime they insert a row the formula is not there and it causes other logic in spreadsheet to fail as i use column E for lookups. Good thought tho.

 

[blaow]

In that case you can use this:

Sub concatenate()
For row = 1 To 5
    Sheets("Sheet1").Cells(row, 5).Value = Sheets("Sheet1").Cells(row, 1).Value & Sheets("Sheet1").Cells(row, 6).Value & Sheets("Sheet1").Cells(row, 9).Value
Next row
End Sub

Change the number of rows to whatever you want
Change all instances of "Sheet1" to the name of your worksheet.

 

[Rick Rothstein]

In that case you can use this:

Sub concatenate()
For row = 1 To 5
    Sheets("Sheet1").Cells(row, 5).Value = Sheets("Sheet1").Cells(row, 1).Value & Sheets("Sheet1").Cells(row, 6).Value & Sheets("Sheet1").Cells(row, 9).Value
Next row
End Sub

Or without using an loop and letting VB figure out the last row..

Sub ConcatBandCandD()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "B").End(xlUp).Row
  Range("A1:A" & LastRow) = Evaluate(Replace("B1:B#&C1:C#&D1:D#", "#", LastRow))
End Sub

 

[titoexcel]

Thank you both for helping out.

I modified the code and its working perfect but i am not sure what some of it means. I was more familiar with Blaow's response but this code seems to do exactly what I wanted it.

Thank you again.

Sub ConcatBandCandD()  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "B").End(xlUp).Row
  Range("E1:E" & LastRow) = Evaluate(Replace("A1:A#&F1:F#&I1:I#", "#", LastRow))
End Sub

 

[blaow]

The simple answer is that my code uses a loop and Rick's uses selecting ranges. His is better, especially if you have huge amounts of data, as VB works more efficiently that way. However, I've never gotten the hang of using ranges and still use loops more often than I should. If you're going to put effort into how the code works, focus on Rick's.

Sub ConcatBandCandD()  
Dim LastRow As Long           'Declares a variable to name the last used row
  LastRow = Cells(Rows.Count, "B").End(xlUp).Row    'Finds the last used row in column B and stores it as LastRow
  Range("E1:E" & LastRow) = Evaluate(Replace("A1:A#&F1:F#&I1:I#", "#", LastRow))  
'Selects a range of E starting from E1 down to the last used row.  Notice that he is concatenating the variable LastRow to the end of the range, so that the line "E1:E" & LastRow will be read as "E1:E15" if LastRow = 15.
'The last part of the code is concatenating columns A, F and I.  In that portion of the line, instead of concatenating LastRow on to every single range, he does it using "#" which is defined at the end as LastRow.
End Sub

Hope this helps!

 

[titoexcel]

The simple answer is that my code uses a loop and Rick's uses selecting ranges. His is better, especially if you have huge amounts of data, as VB works more efficiently that way. However, I've never gotten the hang of using ranges and still use loops more often than I should. If you're going to put effort into how the code works, focus on Rick's.

Sub ConcatBandCandD()  
Dim LastRow As Long           'Declares a variable to name the last used row
  LastRow = Cells(Rows.Count, "B").End(xlUp).Row    'Finds the last used row in column B and stores it as LastRow
  Range("E1:E" & LastRow) = Evaluate(Replace("A1:A#&F1:F#&I1:I#", "#", LastRow))  
'Selects a range of E starting from E1 down to the last used row.  Notice that he is concatenating the variable LastRow to the end of the range, so that the line "E1:E" & LastRow will be read as "E1:E15" if LastRow = 15.
'The last part of the code is concatenating columns A, F and I.  In that portion of the line, instead of concatenating LastRow on to every single range, he does it using "#" which is defined at the end as LastRow.
End Sub

Hope this helps!

Either way it was very helpful. I am doing my best to learn but its pretty challenging right now.