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

samuele2723

New Member
Joined
Jun 26, 2014
Messages
42
HelloFolks,<o:p></o:p>
Situation isthis:<o:p></o:p>
<o:p></o:p>
I have acolumn with a list of words butsometimes cells empty.<o:p></o:p>
I want asoutput in a big cell all the words listed, but skipping blanks.<o:p></o:p>
<o:p></o:p>
Since now Idid everything concatenating in alternate sequence my words column and a columnwith this code:<o:p></o:p>
<o:p></o:p>
Code:
[FONT=Calibri][SIZE=3][COLOR=#000000]=IF(NOT(WORDCELL="");CHAR(10);"")<o:p></o:p>[/COLOR][/SIZE][/FONT]
<o:p></o:p>
<o:p> 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:p></o:p></o:p>
Code:
[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:p></o:p>
<o:p> So now my question is…<o:p></o:p></o:p>
Is possibleto have same result concatenating range some way?<o:p></o:p>
<o:p></o:p><o:p></o:p>
OR<o:p></o:p>
<o:p></o:p><o:p></o:p>
Is there analternative solution for having the list of words, skipping blanks, in theoutput cell without using this technique?<o:p></o:p>
<o:p></o:p>
Thanks<o:p></o:p>
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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
Code:
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,213
Members
453,024
Latest member
Wingit77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top