Advanced filter with blank cells / Dynamic named range with blank cells

[Jason_2112]

The following code filters unique values to a different sheet and fills columns of dynamic named ranges to populate some combo boxes in a user form.

Please Login or Register  to view this content.

The last column to be filtered sometimes has blank cells, and it seems the filter is counting that as a unique value. The problem is that the blank cell is at the top, and the dynamic named range does not skip over this blank cell to extend the named range to the valuable data in the list. Is there a way to change the advanced filter to eliminate blank cells or a way to force the dynamic named range to include at least two rows of data (forcing it to skip the blank cell)?

The formula for the named range is:

Please Login or Register  to view this content.

Thanks for the help,
Jason

[JBeaucaire]

When I do what you've done above, I run a SORT on the resulting columns to make sure stray blanks get sorted off the bottom of the data.

[Jason_2112]

Thank you for pointing me in the right direction. I recorded a macro to determine the language I thought I should use to sort the columns of filtered data, but the code which I modified some, doesn't seem to be working. Here is the code for the entire macro:

Please Login or Register  to view this content.

The ranges are dynamic named ranges, but I don't think I can reference that in this code seeing as the blank cells in the list are causing the named ranges to not expand to include all of the necessary data. I included 35 rows in each, as I don't anticipate having more than that much data in each column.

Thank you for any help.

Jason

[JBeaucaire]

First, the point of breaking one line of code up with _ is to make the code all visible onscreen. You should format the multiple lines so it all shows onscreen, not with the lengthy set of spaces.

The sort can simply refer to the whole column, no need to limit it.

Please Login or Register  to view this content.

[Jason_2112]

I am getting the following error message when I run the sort via VBA.

"The sort reference is not valid. Make sure it's within the data you want to sort, and the first Sort By box isn't the same or blank."

The data to be sorted is on the sheet "CBList" and the header for each column is in A1, C1, and E1.

What do I need to do to reference the appropriate range?

- Jason

[JBeaucaire]

Record a macro of you manually sorting each of those columns after FilterCopy stuff has already run. See if you notice a diff in the resulting code.

[Jason_2112]

Ok, I've figured out that the code will run by itself if the worksheet "CBList" is the active sheet, but if another sheet is active, and the macro is run from that sheet, it will not work.

Here is the code as I have it now.

Please Login or Register  to view this content.

I ran the code for sorting as a separate macro, and it worked fine as long as the CBList sheet was active. Not sure what to do now.

- Jason

[JBeaucaire]

You came 95% of the way to answering your question yourself. You have a parent object reference missing from part of the code making it behave incorrectly if the wrong sheet is active.

Please Login or Register  to view this content.

[Jason_2112]

Thank you for all the help. That did the trick.

- Jason