Why is this advanced filter macro not working

[marcvanderpeet12]

Guys,

I wrote the following VBA code to automate an advanced filter.

Sub Macro1()

Sheets("Nummers").Range("B2:H5817").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("B2:H4"), CopyToRange:=Range("B6:H6"), Unique:=False
End Sub

When I hit it I do not get any records though... Even if I enter no conditions. Any thoughts on what goes wrong here?

Excel is attached.

 

[Chihiro]

See thread linked below.
http://chandoo.org/forum/threads/formula-when-using-advanced-filter.26233/#post-157267

As Narayan's solution shows, you need criteria in text format.

So nest the formula in TEXT().
Ex: =TEXT("> "& J3-$P$3,) in C3

See attached.

 

[Marc L]

Hi Marc !

Your initial attachment seems to work on my side …
Maybe you forgot to reset previous filter as you can see here :
​

​

 

[vletm]

@marcvanderpeet12 Have You check this after [Filtereren]?
@Marc L Why do List range shows ... something ... wrong?

 

[Chihiro]

@vletm
List Range in screen shot shouldn't matter. In most instances, it automatically adjusts to CurrentRegion whenever Advanced Filter button is clicked.

However, as List Range is defined in the code, this range has no relevance when the code is run.

 

[vletm]

@Chihiro Okay,
but if I select wanted ( like Sheets("Nummers").Range("B2:H5817") ) area from this. It neither works!

 

[NARAYANK991]

Hi ,

Everything works correctly , whether through the ribbon menu options or through the code.

The only comment I have is that the signs should be > and < , not > and > as in the file.

Narayan

 

[vletm]

@NARAYANK991 I can still live without this, no work with my Excel2011.
I hope this works with marcvanderpeet12.

 

[Marc L]

Tip (Windows) : call Advanced Filter from the destination worksheet …


​

 

[marcvanderpeet12]

Thanks for your feedback guys, gonna try it out tomorrow and will let you know what works!