AdvancedFilter Works day1, doesn't work day2?

[jvanbonn]

Hi everyone,

Thanks for you wisdom and knowledge.

I have VBA code to create a unique list of names from a repetitive list of names. The list of names is 32 long including the header row, and has 5 unique names.

My goal is to create a unique list of names on Worksheets("Seniors List"). The code worked for me yesterday, but it is not working today. Any ideas why this would occur? How can I get this to work consistently?

Dim w As Worksheet, s As Worksheet
Dim lastSeniorRow As Long
  
  Set w = Worksheets("ACE Locations")
  Set s = Worksheets("Seniors List")
  
  s.Range("A:A").Clear  'Clear the existing list
  
  lastSeniorRow = w.Cells(Rows.Count, 9).End(xlUp).Row  ' =32
    
  w.Range(w.Cells(1, 9), w.Cells(lastSeniorRow, 9)).AdvancedFilter _
    Action:=xlFilterCopy, _
    CopyToRange:=w.Columns("A:A"), Unique:=True

Thanks in advance!

 

[jvanbonn]

Well, I figured out what is going on, but I have know idea why.

When the VBA code executes the AdvancedFilter, there appears to be a residual CriteriaRange:= from a previous filter, which of course is never true, thus no results but the header.

My fix was to add CriteriaRange:="", _ to the code to force no filter criteria.:

Dim w As Worksheet, s As Worksheet
Dim lastSeniorRow As Long
  
  Set w = Worksheets("ACE Locations")
  Set s = Worksheets("Seniors List")
  
  s.Range("A:A").Clear  'Clear the existing list
  
  lastSeniorRow = w.Cells(Rows.Count, 9).End(xlUp).Row  ' =32
    
  w.Range(w.Cells(1, 9), w.Cells(lastSeniorRow, 9)).AdvancedFilter _
    Action:=xlFilterCopy, _
    [B][COLOR=Red]CriteriaRange:="", _[/COLOR][/B]
    CopyToRange:=w.Columns("A:A"), Unique:=True

Why would Excel populate the CriteriaRange option when non was specified?

 

[venkat1926]

yes residual criteria range remains. even if you do not give the criteria range it takes the previous range as valid.