EXCEL VBA AdvancedFilter CopyToRange in empty table

[Roy_Excel_Island_Apps]

Hi Guys,

I just wrote a code to get unique customers, but I need to get them in a existing table which I cleared out. What do I have to place after "CopyToRange" to get my data in the table? At this moment, with the existing code, he lists the data at the beginning of the table, but the table doesn't resize. I tried some resize code but this wouldn't help... Thanks in advance!

Sub GetUniqueCustomerName(tbl As ListObject)

    Dim rng As Range
    Dim tblCustomers As ListObject
    Dim newRow
    Set tblCustomers = shtDASHBOARD.ListObjects("tblCustomers")
   
'Clear the table
    If WorksheetFunction.CountA(shtDASHBOARD.Range("tblCustomers")) > 0 Then
        tblCustomers.DataBodyRange.Delete
    End If
   
'Set range of all customers
    Set rng = tbl.DataBodyRange.Columns(28)
 
'Get the unique customers of the range  
    rng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=tblCustomers, Unique:=True
   

End Sub

 

[Roy_Excel_Island_Apps]

With tblCustomers.Range
tblCustomers.Resize .Resize(.CurrentRegion.Rows.Count)
End With

Did the trick...

 

[eodpapa]

Hi,

where did you insert the above code?
I'm new to VBA and i can't figured it out.