Home » Blogs » Vishesh's blog

Evaluate (VBA) for Concatenation

Vishesh's picture
Submitted by Vishesh on 27 April, 2011 - 16:07
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
    'If(Row(1:7),$B$2:$B$10&$C$2:$C$10)
    
    rng.Value = Evaluate("If(Row(1:" & rng.Rows.Count & ")," & rng.Offset(,  _
      1).Address(, , , True) & " & " & rng.Offset(, 2).Address(, , , True) & ")")
End Sub
Evaluate
AttachmentSize
EvaluateEg.xlsm16.09 KB
EvaluateEg.xls38 KB
»
YasserKhalil's picture

Great and simple Just one

Submitted by YasserKhalil on 5 May, 2015 - 14:42.

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

Thanks advanced

»

concatenate

Submitted by Anonymous on 27 April, 2011 - 16:27.

The zip file opens to show all xml files?

»
Vishesh's picture

Excel both version files

Submitted by Vishesh on 27 April, 2011 - 17:20.

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

»