Calculated values used in formula by macro

Question

I am trying to join many (300+) cells many (100+) times, and believe macro is probably the best way of achieving this.

The internet has suggested using concantenate trickery to achieve this:

=CONCANTENATE(B2:B400) and press F9 whilst highlighting the array, and remove the curly brackets.

This works wonderfully if done by a person. However whilst recording the macro, excel decides to insert the values (i.e. =CONCANTENATE("a", "b", "c")) rather than the trickery. Is there a way around this? I've thought of using sendkeys but, for obvious reasons, I'd rather not.

Or is it better to forego this method entirely and use some sort of loop to join them all together?

Thanks for your help!

Lukas

If I understand you correctly, you want a function that takes a range and returns the concatenated values of all the cells within that range? — arcadeprecinct, Nov 14 '16 at 14:01
I see, I recommend learning how to [http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros](avoid using .Select). — arcadeprecinct, Nov 14 '16 at 14:14

score 0 · Accepted Answer · answered Nov 14 '16 at 11:27

0

solution via an UDF

public function concatrange(r)
s=""
for each c in r
s=s & c
next
concatrange=s
end function

answered Nov 14 '16 at 11:27

h2so4

1,559
1
10
11

Hi H2SO4, Thanks for your help! I tried your solution, but I couldn't get it to work! It returns zero! It's probably something I'm doing wrong! Is it an issue with defining r? – Lukas Ljungstrom Nov 14 '16 at 12:45
I worked it out, adapting your solution: `For count1 = 2 To r Step 1 Dim Ref As Range ActiveCell.Select Set Ref = Selection s = s & Ref ActiveCell.Offset(1, 0).Select Next` Thanks for the help! – Lukas Ljungstrom Nov 14 '16 at 14:09
put the code in a new vba module, and use the function like any other excel function. eg '=concatrange(A1:C1) to concatenate range A1:C1' – h2so4 Nov 14 '16 at 14:09
You should Dim the variables or it won't work when copied into a module using `Option Explicit` – arcadeprecinct Nov 14 '16 at 14:11
I tried to use it as any other function, but there was some issue; maybe what arcadeprecinct identified but I'm not quite sure. Thanks for all the help guys! – Lukas Ljungstrom Nov 14 '16 at 14:45

Calculated values used in formula by macro

1 Answers1