CONCATENATE

[oleg_v]

hi
i need some help.
i have to columns "A" AND "F"
in column "A" is the first name and in column "F" there is
a last name of a person in column "H" i need to bee writen first and last name together with comma separratior.
I need a macro to do this because the length of the data
changes

thanks
oleg

[macropod]

Hi oleg,

What code have you written for this so far?

[oleg_v]

I did not write the code
at this moment i am using
=CONCATENATE(A2,"," & F2)
i rtied to record the macro but i did not know how to
insert a formula whike recording


Oleg

[macropod]

Hi Oleg,

The macro recorder won't do this sort of work for you. However, you've been given help in the past with code that loops through the rows in a column to do something. See:
http://www.vbaexpress.com/forum/showthread.php?t=34334)
There are also many similar examples here at VBA Express. Using the principles in the code from the above thread as a basis, you should be able to develop something that does a similar thing for your existing worksheet. All you need to do is to tell Excel to put the values from columns A & F on the current row into Column H, with a comma separator.

This is not at all difficult to do, requiring as few as 4 lines of code to do the processing.

[shrivallabha]

Use the code below:
At the moment, it will add the result in the sheet1, column G

[VBA]Sub Concatenate()
Dim LastRow As Long
LastRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
With Sheet1
For i = 2 To LastRow
.Range("G" & i).FormulaR1C1 = "=CONCATENATE(RC[-6],"" , "",RC[-1])"
Next i
End With
End Sub
[/VBA]

The main problem with the recorded code is it will never use looping. So you can use the forum or some reference books to learn them on your own.

[Bob Phillips]

[vba]

Sub Concatenate()
Dim LastRow As Long

LastRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
With Worksheets("Sheet1").Range("G2").Resize(LastRow - 1)

.Formula = "A2&"", ""&F2"
.Value = .Value
End With
End Sub
[/vba]

[macropod]

As I said, as few as four lines are needed for the processing:
[VBA]Dim i As Long
For i = 2 To ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row
ActiveSheet.Range("H" & i).Value = ActiveSheet.Range("A" & i).Value & ", " & ActiveSheet.Range("F" & i).Value
Next[/VBA]
An efficient fully-fledged sub would be:
[VBA]Sub Concatenate()
Application.ScreenUpdating = False
Dim i As Long
With ActiveSheet
For i = 2 To .Range("A" & .Rows.Count).End(xlUp).Row
.Range("H" & i).Value = .Range("A" & i).Value & ", " & .Range("F" & i).Value
Next
End With
Application.ScreenUpdating = True
End Sub[/VBA]

[Bob Phillips]

And as I showed, there is absolutely no need to have a loop.

[macropod]

Hi xld,

No dispute there.

The whole point of my original post, though, was to get Oleg to apply some of the logic from previous help he's been given to solving the problem, instead of simply having someone here doing the coding for him.