Advanced Filter Question

[termeric]

I'm wondering why my code sometimes works and sometimes doesn't (giving me a Runtime 1004 The extract range has a missing or illegal field name). I'm using .AdvancedFilter to remove duplicate names from a selected column so that each name is only counted a single time.

I get the error message on this line

            Sheets("Top Broker Input").Range(Cells(1, j), Cells(LegendRange, j)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("Top Broker Legend").Range("D1"), Unique:=True

where j is the column that I want to filter, and legendrange is the last row of that column. Every column that I am trying to filter has a header row and then a list of contact names.

Sub TopSetUp_Legend()

Dim FullRow As Integer, FullRange As Integer, LegendRow As Integer, LegendRange As Integer, ListRange As Integer
Dim FullProducer As String, LegendProdcer As String, Name As String
Dim CFOSelection As String
Dim ColumnID As Integer
Dim columnEnd As Integer
Dim i As Long, j As Integer


Sheets("Top Broker").Range("A3:A75").ClearContents
Sheets("Top Broker").Range("B3:R75").Borders.LineStyle = none
Sheets("Top Broker Legend").Range("A1:A750").ClearContents


ListRange = 2
FullRange = GetCountA("Producer Input", 1)

CFOSelection = Sheets("Top Broker").Range("A1").Value
columnEnd = Sheets("Top Broker Input").UsedRange.Columns.Count
 
'This creates the Top Broker Legend for a given CFO
For i = 2 To columnEnd
    If Sheets("Top Broker Input").Cells(1, i).Value = CFOSelection Then
        LegendRange = GetCountA("Top Broker Input", i)
        
        For LegendRow = 2 To LegendRange
            legendproducer = Sheets("Top Broker Input").Cells(LegendRow, i)
            
            For FullRow = 2 To FullRange
                FullProducer = Sheets("Producer Input").Range("B" & FullRow).Value
                Name = Sheets("Producer Input").Range("A" & FullRow).Value
                
                If FullProducer = legendproducer Then
                    Sheets("Top Broker Legend").Range("A" & ListRange).Value = FullProducer
                    Sheets("Top Broker Legend").Range("B" & ListRange).Value = Name
                    Sheets("Top Broker Legend").Range("C" & ListRange).Value = Sheets("Top Broker Input").Cells(LegendRow, i + 1).Value
                    ListRange = ListRange + 1
                End If
            Next FullRow
        Next LegendRow
        
        j = i + 1
        Sheets("Top Broker Input").Select
        If LegendRange > 1 Then
            Sheets("Top Broker Input").Range(Cells(1, j), Cells(LegendRange, j)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("Top Broker Legend").Range("D1"), Unique:=True
        Else
            Sheets("Top Broker Legend").Range("D1").Value = "No Top Brokers"
        End If
    End If
Next I

End Sub

Is there anything I should be doing differently?


thank you

 

[Norie]

Have you tried clearing out, even deleting, the destination range before you do the filter?

 

[termeric]

Hi Norie, I added a line to delete the column before I filter the information in, and no I get an Error 1004 - Application-defined or object-defined Error

        Sheets("Top Broker Legend").Columns(4).EntireColumn.Delete
        If LegendRange > 1 Then
            Sheets("Top Broker Input").Range(Cells(1, j), Cells(LegendRange, j)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("Top Broker Legend").Range("D1"), Unique:=True
        Else
            Sheets("Top Broker Legend").Range("D1").Value = "No Top Brokers"
        End If

 

[RoryA]

This part is wrong:

Sheets("Top Broker Input").Range(Cells(1, j), Cells(LegendRange, j)).AdvancedFilter

It needs to be:

Sheets("Top Broker Input").Range(Sheets("Top Broker Input").Cells(1, j), Sheets("Top Broker Input").Cells(LegendRange, j)).AdvancedFilter

 

[termeric]

that seems to work. I didn't realize I needed to specify the sheet again, but it does make sense. Thank you both.,

 

[Norie]

You can tidy things up a bit using With.

With Sheets("Top Broker Input")
    .Range(.Cells(1, j), .Cells(LegendRange, j)).AdvancedFilter
End With