How to loop through VBA PIVOTDATA for all relevant items

Question

I have 2 tables. The first is a large tabular report (1,000,000+ lines, 10+ columns) and the second is a summary of this report, which has each unique combination of the first report.

I've attempting to run through all unique combinations of fields, by brute-forcing it, but looping through each line with a 'does columns A-B-C-D-E-F-G-H-I appear previously', just takes far too long.

I've noticed that Pivottables are miraculously faster than my code at ascertaining this and I wanted to simply pull data from one, however I can't see anywhere how to actually use these.

I'm thinking something like this; although I've no idea how to ascertain if something is 'unique':

Dim pvtws, outws as Worksheet
Dim OutputLine as Long

OutputLine = 2 '1 line under heading
For Each Unique_combination_of_fields_A,B,C,D,E,F in pvtws.PivotTables("PivotWorkings_Table")
   With outws
      .Range("A" & ThisLine) = FieldItem_A
      .Range("B" & ThisLine) = FieldItem_B
      .Range("C" & ThisLine) = FieldItem_C
      .Range("D" & ThisLine) = FieldItem_D
      .Range("E" & ThisLine) = FieldItem_E
      .Range("F" & ThisLine) = FieldItem_F
      OutputLine = OutputLine + 1
   End With
Next Unique_combination_of_fields_A,B,C,D,E,F
    
End Sub

I imagine this must be possible, as PivotTables basically already do this themselves. I just want to mimic that in my own report.

Answer 1

Given a range A1:F1000, you can use this code to create a duplicate-free copy to a new sheet as your starting point:

Dim source, target
Set source = Sheets("foo").Range("A1:F1000")
Set target = Sheets.Add.Range("A1")

source.AdvancedFilter Action:=xlFilterCopy, _
                      CopyToRange:=target, _
                      Unique:=True

Then you can iterarate over those unique rows:

Dim unique_row
For Each unique_row In Intersect(target.Parent.Columns(1), target.Parent.UsedRange)
  'Do something with this row
Next