Macro to extract unique values from a range and deposit them in another column

Hi Guys

Hope all well

Please see attached. Spreadsheet.

I am looking for a macro which pulls out all the unique values in a range, in my case A2:F44796 and then deposits them in one column.

This would save me copying and pasting cols B:F into a and doing an advanced filter, and copying uniques to another col..

I think this will do it.

Note that it expects a heading in column G as per your sample workbook.

Sub CollateAndExtractUniqueIDs()

Dim lColumns As Long: lColumns = Cells(1, Columns.Count).End(xlToLeft).Offset(0, -1).Column
Dim lNextRow As Long: lNextRow = 2
Dim lLastRow As Long

Application.ScreenUpdating = False
For i = 1 To lColumns
    lLastRow = Cells(Rows.Count, i).End(xlUp).Row
    Range(Cells(2, i), Cells(lLastRow, i)).Copy _
        Cells(lNextRow, lColumns + 1)
    lNextRow = lNextRow + (lLastRow - 1)
Next 'i

Range(Cells(1, lColumns + 1), Cells(lNextRow - 1, lColumns + 1)).AdvancedFilter _
    Action:=xlFilterCopy, _
    CopyToRange:=Cells(1, lColumns + 2), _
    Unique:=True

Application.ScreenUpdating = True
Application.ScreenUpdating = False

Cells(1, lColumns + 1).Value = "Combined"
Cells(1, lColumns + 2).Value = "Unique"

Application.ScreenUpdating = True
End Sub

Regards

I praise you and may the good Lord bless you and your imediate family, and those in your street. And may Man U, your home town pummel Leeds tonight (sorry Domski - but i do have a few quid on it!)

I've tried a few techniques to get this right and this solution was the best by a mile.

Thanks

You're very welcome. Thanks for the rep.

What do I need to do to get the rest of the family included? ;-)

Kind regards, TMS