VBA or FORMULA for have the list of words in a range

[samuele2723]

HelloFolks,<o></o>
Situation isthis:<o></o>
<o></o>
I have acolumn with a list of words butsometimes cells empty.<o></o>
I want asoutput in a big cell all the words listed, but skipping blanks.<o></o>
<o></o>
Since now Idid everything concatenating in alternate sequence my words column and a columnwith this code:<o></o>
<o></o>

[FONT=Calibri][SIZE=3][COLOR=#000000]=IF(NOT(WORDCELL="");CHAR(10);"")<o:p></o:p>[/COLOR][/SIZE][/FONT]

<o></o>
<o> Everything was fine but now I have a list of 1000 cells and using this technique the function CONCATENATE tells me that I am using too many arguments because I am not concatenating ranges but cell by cell divided in arguments as it is alternate<o></o></o>

[FONT=Calibri][SIZE=3][COLOR=#000000]=CONCATENATE(CH22;CG23;CH23;CG24;CH24;CG25;CH25;CG26;CH26;CG27;CH27;CG28;CH28;CG29;CH29;CG30;CH30;CG31;CH31;CG32;CH32;CG33;CH33;CG34;CH34;CG35;CH35;CG36;CH36;CG37;CH37;CG38;CH38;CG39;CH39;CG40;CH40;CG41;CH41;CG42;CH42;CG43;CH43;CG44;CH44;CG45;CH45;CG46;CH46;CG47;CH47;CG48;CH48;CG49;CH49;CG50;CH50;CG51;CH51;CG52;CH52;CG53;CH53;CG54;CH54;CG55;CH55;CG56;CH56;CG57;CH57;CG58;CH58;CG59;CH59;CG60;CH60;CG61;CH61;CG62;CH62;CG63;CH63;CG64;CH64;CG65;CH65;CG66;CH66;CG67;CH67;CG68;CH68;CG69;CH69;CG70;CH70;CG71;CH71;CG72;CH72;CG73;CH73;CG74;CH74;CG75;CH75;CG76;CH76;CG77;CH77;CG78;CH78;CG79;CH79;CG80;CH80;CG81;CH81;CG82;CH82;CG83;CH83;CG84;CH84;CG85;CH85;CG86;CH86;CG87;CH87;CG88;CH88;CG89;CH89;CG90;CH90;CG91;CH91;CG92;CH92;CG93;CH93;CG94;CH94;CG95;CH95;CG96;CH96;CG97;CH97;CG98;CH98;CG99;CH99;CG100;CH100;CG101;CH101;CG102;CH102;CG103;CH103;CG104;CH104;CG105;CH105;CG106;CH106;CG107;CH107;CG108;CH108;CG109;CH109;CG110;CH110;CG111;CH111;CG112;CH112;CG113;CH113;CG114;CH114;CG115;CH115;CG116;CH116;CG117;CH117;CG118;CH118;CG119;CH119;CG120;CH120;CG121;CH121;CG122)<o:p></o:p>[/COLOR][/SIZE][/FONT]
<o:p></o:p>

<o></o>
<o> So now my question is…<o></o></o>
Is possibleto have same result concatenating range some way?<o></o>
<o></o><o></o>
OR<o></o>
<o></o><o></o>
Is there analternative solution for having the list of words, skipping blanks, in theoutput cell without using this technique?<o></o>
<o></o>
Thanks<o></o>

 

[CharlesChuckieCharles]

Dim c As Range, rng
Dim val1 As String


You only need to Dim (Dimension) things Once in a procedure

 

[samuele2723]

Dim c As Range, rng
Dim val1 As String


You only need to Dim (Dimension) things Once in a procedure

OK, I edited and now macro don’t give error but all macro stops working in the document. I have to close and reopen the docume even for use other macros and VBA gives no error.

Possible? Here is actual code, quite sure something I’ve messed up during merging of 3 codes

Private Sub Worksheet_Change(ByVal Target As Range)
 
    Application.EnableEvents = False
    Dim c As Range, rng
 Dim val1 As String
     If Not Intersect(Target, Range("BP22:BP1020")) Is Nothing Then
   
 Set rng = Range("BP22:BP1020")
 For Each c In rng
 If c.Value <> "" Then
val1 = val1 & "- " & c.Value & Chr(10)
 End If
 Next c
 Sheets("Calcolatore Valutazione").Activate
 Range("R16") = val1
 
    ElseIf Not Intersect(Target, Range("Br22:Br1020")) Is Nothing Then
       
 Set rng = Range("Br22:Br1020")
 For Each c In rng
 If c.Value <> "" Then
val1 = val1 & "- " & c.Value & Chr(10)
 End If
 Next c
 Sheets("Calcolatore Valutazione").Activate
 Range("ag16") = val1
 
    ElseIf Not Intersect(Target, Range("Bt22:Bt1020")) Is Nothing Then
       
 Set rng = Range("Bt22:Bt1020")
 For Each c In rng
 If c.Value <> "" Then
val1 = val1 & "- " & c.Value & Chr(10)
 End If
 Next c
 Sheets("Calcolatore Valutazione").Activate
 Range("av16") = val1
    End If
 
End Sub

 

[CharlesChuckieCharles]

you need to do

Application.EnableEvents = True


do it in the immediate window now to turn the events on again its currently disabling the Change event

 

[samuele2723]

you need to do

Application.EnableEvents = True


do it in the immediate window now to turn the events on again its currently disabling the Change event

So simple. Thanks a lot Charles! topic can be closed