Advanced Filter Only Returning Headings

  • Take a look at the attached which shows a quick sample of a set of data and an accompanying criteria box. I'm trying to run an AdvancedFilter on it, but nothing seems to work. When I run the filter, I get zero rows returned. I've tried filtering using the headings in the criteria box and tried it without... but not much luck. I'm guessing the problem has to do with the data itself, and not the criteria box. Can anyone see what the problem may be?


    Thanks in advance.

  • Re: Advanced Filter Multiple Criteria


    Works fine for me.


    1) Start on Sheet2 (not your data sheet) and activate AdvancedFilter.


    2) Copy to another location.
    List Range: Sheet1!$A$4:$S$33
    Criteria Range: Sheet1!$A$1:$A$2
    Copy To: Sheet2!$A$1


    If you include criteria headings with NO criteria below them, you are adding criteria to say those fields must be blank. So, either exclude them, or use an *


    Hover over the autolink AdvancedFilter and read up on it.

  • Re: Advanced Filter Only Returning Headings


    I can get it to work with this sample data... unfortunately it's still not working with my actual data. With my actual data, I set up a userform that "collects" the data. For instance it contains a dropdown for Analyst, dropdown for Cr Rep, etc. It also has dropdowns for operators (=,<,>,etc). All the values of these comboboxes are fed to another sheet where they are then concatenated into filterable strings. For instance, one string might look like "=Analyst1", another might look like "<$100,000" and so on. I then initiate the AdvancedFilter on the dataset using these criteria. Some of the criteria are filled in, some are not. It could change each time. I've tried filtering the data using the data with formulas, and I've tried to copy/paste values and use that data as my filter set 9thus eliminating the formulas). Nothing has worked.


    I understand the components of AdvancedFilter, i.e. that if you have formulas, then use blank headings or not exact matches... and vice-versa if it is straight data. I also now understand the importance of not having headings without data beneath it. I've tried doing manual AdvancedFilters (the old-fashioned way with the menu drop-down), but I can't even get that to filter UNLESS I DELETE (CLEARCONTENTS) ALL THE CRITERIA AND THEN TYPE IT IN FROM SCRATCH. It works when I do that. Unfortunately, I cannot replicate this using VBA.


    The actual data has several worksheets, multiple userforms, and alot of data (data that is represented fairly by the sample data). I've tried providing only 20 or 30 lines of the data, as well as zipping the file, but it is still too large to post as an attachment.


    I know this was wordy... but if you are able to follow, does anyone have any ideas??? if given permission, I can post the full sample workbook, but recognize it is roughly 120 KB and thus understand if it is too large.


    Thank you.

  • Re: Advanced Filter Only Returning Headings


    You're right, and it's the same with my actual data. Even though I'm concatenating blank values, they must not really be blank or it would be returning a TRUE statement. Which I guess brings me to my next question. From the userform, how do I go about transferring over a "blank" values. I think I've already tried removing all the "" values, but I will check. Any other suggestions on how to go about doing this?

  • Re: Advanced Filter Only Returning Headings


    Any example you post should be relevant to your actual problem, not a fictitious one. It would also stop wasting the time of those trying help if you has bothered to actually explain your real issue.