excel - Removing duplicates from large sheet -

i want remove rows based on duplicate cells in column large sheet, without leaving duplicate sample (like "remove duplicates" excel command does). if have:

1 2 2 3 

i want, result:

1 3 

this can accomplished conditional formatting, filtering or sorting duplicates , deleting filtered data, process slow large sheet. conditional formatting takes second, clicking on filter takes around 5min display filter context menu , additional 20-30min actual filtering based on color. tried process on different pcs 4 cores , plenty of ram , 100.000 rows sheet

i thought write vba, iterate column cells , if cell colored, delete entire row (this possible in excel 2010, cells().displayformat) processing takes more time.

can suggest faster way remove duplicates on large sheet?

edit: note have used 2 functions. of this, test function test whether function works (which have modify per scenario).

also, filled cell a1 a100000 test values. please modify per needs.

option explicit  function getuniqueitems(byval src range) variant dim returnvalue  dim dictofitemswith1value dim dictofitemswithmorethan1value  dim countofcells long dim counter long  dim srcvalues variant dim currentvalue dim cell range  srcvalues = src.value countofcells = src.cells.count  set dictofitemswith1value = createobject("scripting.dictionary") set dictofitemswithmorethan1value = createobject("scripting.dictionary")  counter = 1 countofcells     currentvalue = srcvalues(counter, 1)     if dictofitemswithmorethan1value.exists(currentvalue)         dictofitemswithmorethan1value(currentvalue) = dictofitemswithmorethan1value(currentvalue) + 1     else         if not dictofitemswith1value.exists(currentvalue)             dictofitemswith1value.add currentvalue, 1         else             dictofitemswith1value.remove currentvalue             dictofitemswithmorethan1value.add currentvalue, 1         end if     end if next  redim returnvalue(1 dictofitemswith1value.count, 1 1) dim key  counter = 1 each key in dictofitemswith1value.keys     returnvalue(counter, 1) = key     counter = counter + 1 next  getuniqueitems = returnvalue end function sub test() debug.print dim uniquevalues uniquevalues = getuniqueitems(range("a1:a100000"))  range("a1:a100000").clearcontents range("a1").resize(ubound(uniquevalues, 1)) = uniquevalues  debug.print end sub