Find unique values in column - in a SINGLE CELL FORMULA; can it be done please?

@thiefofthings - thanks for this - I suspected that the formula path may be tricky so thanks for confirming what Google results had me fearing.

If I want to run this on column C, as opposed to A, just need to alter the column references to A in the VBA yes?

Originally Posted by iantix

@thiefofthings - thanks for this - I suspected that the formula path may be tricky so thanks for confirming what Google results had me fearing.

If I want to run this on column C, as opposed to A, just need to alter the column references to A in the VBA yes?

Yep, just change from A to C, and you end up with this:

Sub UniqueValues()

With Application
    .ScreenUpdating = False     'turns off screen updating
End With


Dim x As Long

x = ActiveSheet.Range("C1048576").End(xlUp).Row     'find last non-blank row in column C

With ActiveSheet.Range("C2:C" & x)                          'copy entries from C2 to the last non-blank row
    .Copy
End With

With ActiveSheet.Range("M1")                        'paste copied entries to empty column
    .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End With

With ActiveSheet.Range("M:M")                       'removes duplicate email addresses
    .RemoveDuplicates Columns:=1, Header:=xlNo
End With

With ActiveSheet.Range("D2")                        'counts non-blank entries in column M
    .Formula = "=COUNTA(M:M)"
    .Value = .Value
End With

With ActiveSheet.Range("M:M")                       'tidies up
    .ClearContents
End With


With Application
    .ScreenUpdating = True
End With

End Sub