AdvancedFilter and Blank Cells

[mtnbiker98]

Hello, All.

I am working with AdvancedFilter to pull a list of unique values from a Table (ListObject) in excel. The table column in question has many blank cells in it by design.

When I use the code

Please Login or Register  to view this content.

on that column I get nothing back.

If I fill all the blank cells with N/A for example (which I dont really want to do) I get back what I would expect to see.

Does AdvancedFilter have an aversion to blank cells? None of the MS documentation i read on the method indicated that it does.

Thank you.

[tehneXus]

Hi,
Usually the advanced filter has no issued with blank cells, at least I've never noticed such thing. Do you have an example?
In case you need this unique list in vba for further processing I'd recommend to use a dictionary rather than the advanced filter.

[mtnbiker98]

Hi,
Usually the advanced filter has no issued with blank cells, at least I've never noticed such thing. Do you have an example?
In case you need this unique list in vba for further processing I'd recommend to use a dictionary rather than the advanced filter.

Sure, here is the dataset. I cut out all the supporting data but, this is the column in the table (called Categories) that I am trying to do an advanced filter on. Again, in it's current form and with the code above, nothing is pasted into B1:BX. However, if I add N/A or some other data to the blank cells, I get back all the unique results including N/A...which is not really what I am looking to do. I will have to write some other code to exclude N/A.

With the dictionary, are you suggesting to read the data into a dictionary, then sort? Is that programatically quicker than advancedFilter?

[tehneXus]

Hi,

The advanced Filter needs a heading in the 1st row selected, unfortunately by referring to a column in a table (listobj) only the data is passed to the range object in vba, a workaround:

Please Login or Register  to view this content.

Note that you cannot use the adjacent column as target range, this will result in a application defined error as you cannot have 2 identical headings in a table.

dictionary solution:

Please Login or Register  to view this content.

Filter vs dictionary:
- When sheet is protected you cannot use filter
- Formatting is kept using filter, dic provides values only
- dic is 3 times faster (based on processing your test data 1000 times)