title: Copy the values of a collection to a 2D array in VBA
body: <p>I seem to be banging my ahead against the proverbial brick wall. I have a loop which runs and populates a collection. There are approximately 20000 rows and 11 columns. I need to be able to get the contents of the collection into a variant array so that I can do a bulk copy to a worksheet. The reason I was using a collection is for the inherent de-duplication of entries. </p>
  
  <p>Please could someone offer a suggestion as to how to achieve this. I assume I am missing something straightforward but if I dont use a collection I need to dedupliate 200k+ rows.</p>
  
  <p>Thanks in advance for all your help</p>
  
  <p>EDIT</p>
  
  <p>Here is the actual code. As I mentioned above, the problem is not getting the data into the collection (MyCollection), its getting it out again! </p>
  
  <p>EDIT</p>
  
  <p>The flow of data starts in a worksheet which is then copied into an array called ArrayOrg. The array is looped through and when certain conditions are satisfied, a record is added to the ArrayOrg1 array. Please see the code below. </p>
  
  <pre><code>    For intI = 1 To UBound(ArrayOrg())
              If ArrayOrg(intI, 7) = "cMat" And ArrayOrg(intI, 5) = "Plant" Then
  
                  ArrayOrg1_cMat(Org1Count_cMat, 0) = ArrayOrg(intI, 1)           'User ID
                  ArrayOrg1_cMat(Org1Count_cMat, 1) = ArrayOrg(intI, 2)           'BR ID
                  ArrayOrg1_cMat(Org1Count_cMat, 2) = ArrayOrg(intI, 3)           'Scenario
                  ArrayOrg1_cMat(Org1Count_cMat, 3) = ArrayOrg(intI, 4)           'Role
                  ArrayOrg1_cMat(Org1Count_cMat, 4) = ArrayOrg(intI, 5)           'Controlling Field
                  ArrayOrg1_cMat(Org1Count_cMat, 5) = ArrayOrg(intI, 6)           'Controlling Field Value
                  ArrayOrg1_cMat(Org1Count_cMat, 6) = ArrayOrg(intI, 7)           'Webapp
                  Org1Count_cMat = Org1Count_cMat + 1
  Next intI
  
  Dim MyCollection As Collection
      Dim ArrayTemp() As Variant
      Set MyCollection = New Collection  
  
      For intI = 0 To UBound(ArrayOrg1_cMat())
          For intJ = 0 To UBound(ArrayOrg2_cMat())
                  If ArrayOrg2_cMat(intJ, 0) = ArrayOrg1_cMat(intI, 0) Then
                          If ArrayOrg2_cMat(intJ, 1) = ArrayOrg1_cMat(intI, 1 Then                             If ArrayOrg2_cMat(intJ, 2) = ArrayOrg1_cMat(intI, 2) Then                                 If ArrayOrg2_cMat(intJ, 3) = ArrayOrg1_cMat(intI, 3) Then                                     
  
                                      ArrayTemp(0, 0) = ""                                    'Name
                                      ArrayTemp(0, 1) = ArrayOrg1_cMat(intI, 0)               'AD ID
                                      ArrayTemp(0, 2) = ""                                    'Email
                                      ArrayTemp(0, 3) = ""                                     'Requester
                                      ArrayTemp(0, 4) = ArrayOrg1_cMat(intI, 6)               'Webapp
                                      ArrayTemp(0, 5) = ArrayOrg1_cMat(intI, 2)               'Scenario
                                      ArrayTemp(0, 6) = ArrayOrg1_cMat(intI, 3)               'Role
                                      ArrayTemp(0, 7) = "PL"                                 'Business Unit
                                      ArrayTemp(0, 8) = "NONE"
                                      ArrayTemp(0, 9) = "NONE"
                                      ArrayTemp(0, 10) = "NONE"
                                      ArrayTemp(0, 11) = ArrayTemp(0, 0) &amp; ArrayTemp(0, 1) &amp; ArrayTemp                                              (0, 2) &amp; ArrayTemp(0, 3) &amp; ArrayTemp(0, 4) _
                                                        &amp; ArrayTemp(0, 5) &amp; ArrayTemp(0, 6) &amp; ArrayTemp                                                 (0, 7) &amp; ArrayTemp(0, 8) &amp; ArrayTemp(0, 9) _
                                                        &amp; ArrayTemp(0, 10) '### This is the key for the collection
  
                                      On Error Resume Next
                                      MyCollection.Add ArrayTemp, ArrayTemp(0, 11)
                                      On Error GoTo 0                                    
                                  End If
                              End If
                          End If
                  End If
          Next intJ
      Next intI
  '#### THIS IS WHERE THE PROBLEM IS
  For intI = 0 To MyCollection.Count  
      ArrayOutput(intI, 0) = MyCollection.Item(intI)  
  Next intI  
  </code></pre>
  
  <p>Thanks
  Kevin</p>