Autofilter using unique values required in Excel

Question

The code below gets a list of unique contacts from column F and then outputs them in the Immediate window.

How would I use this data created to run a series of autofilters on the data (i.e. within a for loop, one at a time). In between each autofilter, the data will be copied and saved to a new spreadsheet.

Sub GetPrimaryContacts()

Dim Col As New Collection
Dim itm
Dim i As Long
Dim CellVell As Variant

'Get last row value
LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row

'Loop between all rows to get unique values
For i = 3 To LastRow
    CellVal = Sheets("Master").Range("F" & i).Value
    On Error Resume Next
    Col.Add CellVal, Chr(34) & CellVal & Chr(34)
    On Error GoTo 0
Next i

For Each itm In Col
    Debug.Print itm
Next

End Sub

Why would you want to "run a series of autofilters"? The user could only see the result of the last autofilter operation? — pintxo, May 10 '14 at 13:08
@cmmi - in between each autofilters, the data will be copied and saved to a new spreadsheet. — theshizy, May 10 '14 at 13:08
so you actually want to copy the rows for each unique attribute to a separate worksheet? — pintxo, May 10 '14 at 13:10
**A single new worksheet or a new worksheet for each value??** — Gary's Student, May 10 '14 at 13:12
@cmmi - a seperate excel document saved to the user's C:\ drive — theshizy, May 10 '14 at 13:12

score 1 · Accepted Answer · answered May 10 '14 at 13:19

Pseudo Code, sorry I haven't used VBA for a while:

dictionary as Dictionary

for each row in sheet
  attribute = Cell(A,1)
  if (attribute not in dictionary) then
    dictionary.put(attribute, newWorkbook)
  end if
  workbook = dictionary.get(attribute)
  'copy row to workbook
end for

This code could be highly optimized by first storing the original data in a array of type Variable and also storing the outputs for the diverse workbooks in arrays of type Variable. Only once the loop is finished you actually create the output workbooks and write the contents from the arrays to the workbooks.

That way we do not need to first look for the unique values and then repeatedly apply the AutoFilter which will be a lot slower and less easy to maintain.

I'm not too sure how to implement this solution - can you please point me in the direction of any references? — theshizy, May 10 '14 at 13:43
@jmb Resources: [Dictionary](http://stackoverflow.com/questions/915317/does-vba-have-dictionary-structure), [Performance](http://blogs.office.com/2009/03/12/excel-vba-performance-coding-best-practices/) — pintxo, May 10 '14 at 13:45

Autofilter using unique values required in Excel

1 Answers1