title: Macro - Create List - Count - Sort
body: <p>I have a spreadsheet where I import 100,000 rows of data, split it out into separate words then consolidate the list (I’ve reduced the sample size for ease)
  The macro takes all the separated words and puts these into a list and counts the number of times each unique word turns up in the full selection of data.</p>
  <p>This has run for several large sized data sets but now I’m getting an error and a small sample and can’t see how to fix this. I have attached an example data set and also my code.</p>
  <p>I have tried removing &quot;_&quot; &amp; &quot;-&quot; thinking characters might be stopping the macro, but this did not help.</p>
  <p>Data is split from an e-mail that is on tab &quot;SCRUB&quot; &quot;B3:B&quot; and text to col listed &quot;SCRUB&quot; &quot;C3:XFD&quot;
  The code should take the individual words in &quot;SCRUB&quot; &quot;C3:XFD&quot; and list &quot;LIST&quot; &quot;A2:A&quot; &amp; count &quot;LIST&quot; &quot;B2:B&quot;. Then sort by &quot;LIST&quot; &quot;B&quot;</p>
  <p>Run-time error <strong>'1004': Unable to get the CountIf property of the WorksheetFunction class</strong></p>
  <p>Below is the code:</p>
  <pre><code>Sub ListCreate()
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  Dim s As Worksheet, ss As Worksheet
  Set s = Sheets(&quot;SCRUB&quot;)
  Set ss = Sheets(&quot;List&quot;)
  ss.Range(&quot;A2:A650000&quot;).ClearContents
  lr = s.Range(&quot;B&quot; &amp; Rows.Count).End(xlUp).Row
  counter = 2
  For i = 3 To lr
      For j = 3 To s.Cells(i, Columns.Count).End(xlToLeft).Column
           txt = s.Cells(i, j)
             If txt &lt;&gt; &quot;&quot; And WorksheetFunction.CountIf(ss.Range(&quot;A:A&quot;), txt) = 0 Then
              ss.Range(&quot;A&quot; &amp; counter).Value = txt
              ss.Range(&quot;B&quot; &amp; counter).Value = WorksheetFunction.CountIf(s.Range(&quot;C:XFD&quot;), txt)
              counter = counter + 1
          End If
      Next j
  Next i
      lr = ss.Range(&quot;A&quot; &amp; Rows.Count).End(xlUp).Row
      ss.Sort.SortFields.Clear
      ss.Sort.SortFields.Add Key:=ss.Range(&quot;B2:B&quot; &amp; lr), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
      With ss.Sort
          .SetRange ss.Range(&quot;A1:B&quot; &amp; lr)
          .Header = xlYes
          .MatchCase = False
          .Orientation = xlTopToBottom
          .SortMethod = xlPinYin
          .Apply
      End With
  
  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
  
  
  End Sub
  
  'Clean the string of text
  Function cleanString(text As String) As String
      Dim output As String
      Dim c 'since char type does not exist in vba, we have to use variant type.
      For i = 1 To Len(text)
          c = Mid(text, i, 1) 'Select the character at the i position
          If (c &gt;= &quot;a&quot; And c &lt;= &quot;z&quot;) Or (c &gt;= &quot;0&quot; And c &lt;= &quot;9&quot;) Or (c &gt;= &quot;A&quot; And c &lt;= &quot;Z&quot;) Then
              output = output &amp; c 'add the character to your output.
          Else
              output = output &amp; &quot; &quot; 'add the replacement character (space) to your output
          End If
      Next
      cleanString = output
  End Function
  </code></pre>
  <p>Thank you in advance for your help</p>
  <p>Sample of 10 lines of e-mails which the macro breaks on, with whitespace (Hi denotes new cell)</p>
  <pre><code>Hi Team,
  
   
  
  Please advise if the trades below need to be settled as Gross or Net.
  
   
  
  Thanks
  
   
  
   
  
  CustomerBroker IDBroker/BicTrade_dateValue_DateBuy_CcyBuy_AmountSell_CcySell_AmountFx_Exch_rateClient_Ref
  
  00000000AAAAAAAA00000000000EUR2,224.00TRY16,548.200.4400000000000
  
  00000000AAAAAAAA000000000000GBP200,082.99SEK286,619.000.690000000000
  
  
  
  
  Hi Team,
  
   
  
  Please advise if the trades below need to be settled as Gross or Net.
  
   
  
  Thanks
  
   
  
   
  
  CustomerBroker IDBroker/BicTrade_dateValue_DateBuy_CcyBuy_AmountSell_CcySell_AmountFx_Exch_rateClient_Ref
  
  00000000AAAAAAAA0000000000000000NOK33,202.00EUR3,183.2510.4300000000000
  
  00000000AAAAAAAA0000000000000000SEK571,826.00EUR53,478.6910.690000000000
  
  00000000AAAAAAAA0000000000000000SEK1,727,152.00EUR161,527.8610.6900000000000
  
  00000000AAAAAAAA0000000000000000EUR18.70SEK200.0010.69000000000000
  
  00000000AAAAAAAA0000000000000000EUR55,083.45SEK588,985.0010.6900000000000
  
  00000000AAAAAAAA000000000000EUR18.7SEK200.0010.11000000000000
  
  
  
  
  
  Hi Good day.
  
   
  
  Following up on the below request.
  
   
  
  Thank you.
  
  Hi
  
  ++ back up attached 
  
   
  
  Name 
  
   
  Hi Name.
  
  
  
  The checklist is included in the 'blaht Account Forms' section on the website.
  
  
  
  Please find attached a copy of the checklist.
  
  
  
  
  
  
  -----Original Message-----
  
  
  
   
  
  Hi Good Afternoon 
  
   
  
  We need to urgently organize a call with yourselves this afternoon to confirm some payment details.
  
   
  
  The account in question is TYT0000000 and it concerns a trade TD 13/06/2022 SD 15/06/2022 
  
   
  
  We need to confirm the bank details this afternoon.
  
   
  
  Can you kindly confirm a time today in which we may call you.
  
   
  
   
  
  Kind regards,
  
   
  
  
   
  
  
  
  
  
  .
  
   
  
   
  
   
  
   
  
   
   Hi Team,
  
   
  
  Please advise on trade below as the holdings are in Germany but trade is instructed to settle in Clearstream. Please review and advise
  
   
  
  Thanks
  
   
  
   
  
  Order IdOrder TypeClient ReferenceCustody Position StatusDiary Remark IndicatorCancellation StatusDepository ReferenceStatusSecurity Account IdTrade DateSettlement DateSecurity NumberNet Settlement AmountQuantityTrade CurrencyTransaction Type
  
  0000008Delivery Versus Payment000000000ASDNoCancellation Not Initiated To Be Authorised0000:DAA INV.SCV-EUROP CORP.SUST.B24/06/202228/06/2022DE000YYY0GU5:AAAAA-HAN HY 5,5% 16-26.09.23EUR : 1,000,407.26FAMT : 20,300,000.000000000000000EURSettlement Deliver
  
  
  
   
  
  
  
   
  
  .
  
   
  
  Hi 
  
  Thank you for being so tenacious and bringing this matter to resolution, .  Well done !  Appreciate the update.  
  
   
  
  
  Hi 
  
  Anytime!
  
   
  
  
  
   
  
  
  
  Hi human, 
  
   
  
  Please find attached COAS call back for your approval. 
  
   
  
   
  
  Thank you 
  </code></pre>