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
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments