Can't remove duplicates from range

Hello,
I have an excel table with several columns two of which I am interested in. What I am trying to do is filter the first column with a specific criterion and then copy the visible values from the other column into a range object. After that I need to remove duplicates. The problem is I get an error. Here's the code. There are a lot of duplicates. Please tell me what's wrong or suggest a better way to achieve the task I'm trying to do.

Sub Begin()
    
    Dim tbl As ListObject
    Set tbl = Worksheets("Sheet1").ListObjects("Table1")

    WorkSheet.AutoFilterMode = False
    tbl.Range.AutoFilter Field:=8, Criteria1:="DUKESTREET_II-2"
    
    Dim rng1 As Range
    Set rng1 =  tbl.ListColumns("TGT CELL NAME").DataBodyRange.SpecialCells(xlCellTypeVisible)
    MsgBox rng1.Count
    rng1.RemoveDuplicates Columns:=1, Header:=xlNo
    MsgBox rng1.Count
    
End Sub

Thanks

Originally Posted by BeachRock

Check this out: http://spreadsheets.about.com/od/dat...s-tutorial.htm

That's not what I'm asking. That's not VBA !!

How can a non VBA method help solve a VBA related problem ?

Hi !

Originally Posted by medwatt

The problem is I get an error.

Very clear ! Shall we guess the error message and on which line ?!

Here is where I get the error:
rng1.RemoveDuplicates Columns:=1, Header:=xlNo

Also if I say rng1.rows.count I get 12 but if I say rng1.count I get 71 which is the actual number.

The interesting one for this method is the Columns.Count and on which one are the duplicates,
see vba help of RemoveDuplicates …

Maybe the issue came 'cause of an active filter (non-contiguous cells), try without
or after pasting visible cells on another place and thus launch on it the RemoveDuplicates method …