title: Count number of non-empty fields in Excel VB array's column
body: <p>I currently read a 2-dimensional range into an Excel VBA array like so:</p>
  
  <pre><code>Set Ws = Sheet1
  Ws.Activate
  
  LastRow = Ws.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
  LastCol = Ws.Cells.Find("*", searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
  
  ReDim elements(0 To LastRow - 2, 0 To LastCol - 2)
  elements = Ws.Range(Cells(2, 1), Cells(LastRow, LastCol))
  </code></pre>
  
  <p>The range is 25 rows by 11 columns. However, not all cells in the range have values so some of the values in the array are 'empty'. </p>
  
  <ul>
  <li>col A has 25 items</li>
  <li>col B has 16</li>
  <li>col K has 12...</li>
  </ul>
  
  <p>I need to loop through this array and create a second array, which will be a <strong>"Cartesian product" of the values from the first one</strong>. In order to determine how many times I need to loop I need to figure out how many items there are in each of the arrays columns ("dimensions"?).</p>
  
  <p>Here is an attempt of my loop:</p>
  
  <pre><code>Row = 0
  
  For i = 1 To 25  'numElements in column 1
   For j = 1 To 3    'numElements in column 6           
    For k = 1 To 5    'numElements in column 7
     For l = 1 To 14    'numElements in column 8
      For m = 1 To 6     'numElements in column 10
       For n = 1 To 12     'numElements in column 11
  
        cartesian(Row, 0) = elements(i, 0)
        cartesian(Row, 1) = elements(i, 1)
        cartesian(Row, 2) = elements(i, 2)
        cartesian(Row, 3) = elements(i, 3)
        cartesian(Row, 4) = elements(i, 4)
        cartesian(Row, 5) = elements(j, 5)
        cartesian(Row, 6) = elements(k, 6)
        cartesian(Row, 7) = elements(l, 7)
        cartesian(Row, 8) = elements(l, 8)
        cartesian(Row, 9) = elements(m, 9)
        cartesian(Row, 10) = elements(n, 10)
  
         Row = Row + 1
       Next n         
      Next m
     Next l
    Next k
   Next j
  Next i
  </code></pre>
  
  <p>Any help appreciated :)</p>
  
  <p><strong>EDIT 1:</strong> </p>
  
  <p>This is the range that I read into array1:</p>
  
  <pre><code>Austria  sem  jan
  Belgium  gdn  feb
  France        mar
  US            apr
                may
                jun
  </code></pre>
  
  <p>I need to be able to count how many "items" there are in column 1, column 2 and column 3 in order to multiply them. That way I will know how big I need to ReDim second array. </p>
  
  <p>This is what I need in the array 2 and to finally write back into another sheet:</p>
  
  <pre><code>Austria  sem    jan
  Austria  sem    feb
  Austria  sem    mar
  Austria  sem    apr
  Austria  sem    may
  Austria  sem    jun
  Austria  gdn    jan
  Austria  gdn    feb
  Austria  gdn    mar
  Austria  gdn    apr
  Austria  gdn    may
  Austria  gdn    jun
  Belgium  sem    jan
  Belgium  sem    feb
  Belgium  sem    mar
  Belgium  sem    apr
  Belgium  sem    may
  Belgium  sem    jun
  Belgium  gdn    jan
  Belgium  gdn    feb
  Belgium  gdn    mar
  Belgium  gdn    apr
  Belgium  gdn    may
  Belgium  gdn    jun
  </code></pre>
  
  <p>etc.</p>