Add entire row to collection and print to worksheet

[Certified]

Hi,

I have a spreadsheet with close to 6000 rows and 200 columns.

I want to pull data that meets certain criteria, place that data in a collection and paste the results on a fresh worksheet.

Right now I have the data in a collection, but I can't figure out how to print the data.

In my locals window it looks like this...
item 1
-------Item 1(1)
-----------item(1,1) "Company"

Here is the code I have so far..

Sub IncludeOnlyGEMS(erMain As Long, ecMain As Long)

    Dim rngMasterLocation As Range
   
    'Start Range at row 2 to exclude Source information

    Set rngMasterLocation = Range(Sheet6.Cells(2, 1), Sheet6.Cells(erMain, ecMain))  
   
    Dim collMasterLoc As New Collection
   
    Dim i As Integer
   
    For i = 2 To erMain
   
        If Sheet6.Cells(i, 23).Value <> "" Then
       
            If Sheet6.Cells(i, 23).Value <> "GD999" Then
           
                If Sheet6.Cells(i, 23) <> "NA" Then   
                   
                    collMasterLoc.Add Sheet6.Range("A" & i & ":" & "FQ" & i).Value
           
                End If
           
            End If
       
        End If
   
    Next i

I am at a lost. Any ideas?

 

[Fluff]

E=Rather than using a collection, why not use an array like

Sub IncludeOnlyGEMS(erMain As Long, ecMain As Long)

   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, nr As Long
   
   'Start Range at row 2 to exclude Source information
   
   Ary = Range(Sheet6.Cells(2, 1), Sheet6.Cells(erMain, ecMain)).Value2
   ReDim Nary(1 To UBound(Ary), 1 To UBound(Ar, 2))
   
   For r = 2 To erMain
      If Ary(i, 23).Value <> "" And Ary(i, 23).Value <> "GD999" And Ary(i, 23) <> "NA" Then
         nr = nr + 1
         For c = 1 To UBound(Ary, 2)
            Nary(nr, c) = Ary(r, c)
         Next c
      End If
   Next r
   Sheet7.Range("A2").Resize(nr, UBound(Nary)).Value = Nary
End Sub

 

[Certified]

E=Rather than using a collection, why not use an array like

Sub IncludeOnlyGEMS(erMain As Long, ecMain As Long)

   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, nr As Long
  
   'Start Range at row 2 to exclude Source information
  
   Ary = Range(Sheet6.Cells(2, 1), Sheet6.Cells(erMain, ecMain)).Value2
   ReDim Nary(1 To UBound(Ary), 1 To UBound(Ar, 2))
  
   For r = 2 To erMain
      If Ary(i, 23).Value <> "" And Ary(i, 23).Value <> "GD999" And Ary(i, 23) <> "NA" Then
         nr = nr + 1
         For c = 1 To UBound(Ary, 2)
            Nary(nr, c) = Ary(r, c)
         Next c
      End If
   Next r
   Sheet7.Range("A2").Resize(nr, UBound(Nary)).Value = Nary
End Sub

That is perfect. Thanks.

 

[Fluff]

You're welcome & thanks for the feedback.