Advanced Filter Question

termeric

Board Regular
Joined
Jun 21, 2005
Messages
245
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

Code:
            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.



Code:
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
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
Have you tried clearing out, even deleting, the destination range before you do the filter?
 

termeric

Board Regular
Joined
Jun 21, 2005
Messages
245
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

Code:
        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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,506
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
This part is wrong:

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

It needs to be:

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

termeric

Board Regular
Joined
Jun 21, 2005
Messages
245
that seems to work. I didn't realize I needed to specify the sheet again, but it does make sense. Thank you both.,
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
You can tidy things up a bit using With.
Code:
With Sheets("Top Broker Input")
    .Range(.Cells(1, j), .Cells(LegendRange, j)).AdvancedFilter
End With
 

Watch MrExcel Video

Forum statistics

Threads
1,109,045
Messages
5,526,447
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top