Search code examples
vbaexceladvanced-filter

Advanced filter error

I am trying to create a unique list but the first value in the list is a blank and that seems to be causing me a problem. When I use a basic advanced filter it essentially just names the range 'Extract' and copies the formatting. So F2 equals `Extract' as below, enter image description here

I have tried many approaches using the criteria range but nothing seems to works. Ideally I want this to be VBA code but the manual Advanced Filter is not working. To clarify I am searching like this,

enter image description here

I see a similar question here, it's just not working for me. Does anyone have any ideas what I am doing wrong? Is it the 'Copy into new location' option that is messing this up? Ideally I need it to be unique fields but I seem to be getting the same results, with or without the unique fields only box ticked.

Eventually I want this to be the code but, when I run the below in VBA I get a Run-Time error. Range("E2:E5").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _ "h1:h2"), CopyToRange:=Range("F2"), Unique:=True

EDIT

enter image description here

Solution

  • Use <> for the criteria to exclude blanks.

    enter image description here

    btw, your List Range should be E1:E5 and H1 should be Offset values. Advanced Filters require a header.