Evaluate (VBA) for Concatenation

Vishesh's picture
Just learnt it a few days back from one of my office colleagues...how to concatenate a range of values with other range without having to run a loop. Download the attachment to see how it works. Also check its code and modify it to suit your needs.
Sub EvalutateExample()
    'Concatenate values in range B2:10 & C2:C10 and
    'display the result in A2:A10
    Dim rng As Range
    Set rng = Sheet1.Range("A2:A10") 'Change it as per you requirement
    'This is what is generated as a parameter of Evaluate in this procedure
    rng.Value = Evaluate("If(Row(1:" & rng.Rows.Count & ")," & rng.Offset(,  _
      1).Address(, , , True) & " & " & rng.Offset(, 2).Address(, , , True) & ")")
End Sub
EvaluateEg.xlsm16.09 KB
EvaluateEg.xls38 KB
YasserKhalil's picture

Great and simple Just one

Great and simple
Just one point how to add - between the results
for example :
P - 45333

Thanks advanced


The zip file opens to show all xml files?

Vishesh's picture

Excel both version files

I have uploaded files for both excel versions 2007 and 2003. Check the other file as well...the one with .xls extension.