Hide Rows with blank cell

Attached is a test worksheet to review with this problem.

If you access the drop down list and select a color, only the two rows associated with that color should show. The grayed out area should always be visible.

If you select "All" from the drop down then all the colors should show.

You can find each color command in the "ColorMacros" module and the drop down command in the "DropDown" module. While these two commands are a little slow, they work. (Extra credit if you want to look at the "ColorMacros" and provide suggestion on how not to have to write a code for each color because I will be adding more colors running across).

The part I am having an issue with is once a color is select, in this case "Ivory101" not only should it hide all the other color columns, but it should also hide any rows where column "J" is blank starting (or ending depending on the code) with Row 9.

For all other colors it would be the column that shows the "Kg Needed" so in the case of Sage it would be "L".

Does the macro have to identify the specific "Kg Needed" column or can it be coded so that the "10th" column visible on the screen is where the hiderow macro should execute?

Every combination i played with either causes a debugging error or if I switch from one color to another color, the hidden rows from the first color stay hidden.

And of course if you select ALL then everything should be visible.

I dont know if this matters but the column count on the actual spreadsheet runs to EA/EB and the rows currently run down to about 10,000 give or take.

Any bit of help would be greatly appreciated!

I apologies if my original explanation is not clear:

If you run the drop down and select "Ivory101" everything that needs to hide, does. In Column J you will see a numbers in different cells.

When you select "Ivory101" all columns that currently hide should hide and all rows in the range in this case J9:J19 that do not have a number should hide so J10:J12, J15, & J17:J19 should all hide.

Now lets say you go back to the drop down list and you select "BarnRed". All the columns that are supposed to hide do so and now you should see numbers in cells P10, P13, & P18. All the other rows in the range P9:P19 that do not have a number in column P should hide.

So i guess the right way to say this is which ever "color" is visible, based on the column which has a heading of "Kg Needed", if a blank cell is found after row 9, the entire row should hide.

If "ALL" is selected then every row and column should be visible.

Leith,

First off let me say WOW!! and then Thank you.

Looks spectacular.

I am going to work through it today and will post any questions or problems I might run into.

Should I mark this thread as "SOLVED" or keep it open for a few days just in case I need to post a question?

Leith,

I trust you are having a nice weekend, and again thanks for the excellent code.

First off I built the spreadsheet based on the exact layout i originally had and with your code, its producing everything I need so far in the test runs.

I did find an problem when I reformatted the data and tried a few different things to get the code to "match back up" but cannot figure it out.

The original worksheet started in column D row 5 and the first color references started in JK (Don't forget each color uses two columns). If I delete the columns so that the sheet starts in column A row 5, the sheet is not hiding and unhiding correctly.

Below is the original code along with my notes on what I changed....

Please Login or Register  to view this content.

I also made the following change as well:

Please Login or Register  to view this content.

I am in the process of reformatting all the ColorMacro since the columns have changed so if you need the workbook to review this issue, i should have it done shortly and will put it up for review.

Thanks!

Through trail and error I figured out what needed to be changed which is:

Please Login or Register  to view this content.

Unfortunately I do not understand why my changes made the sheet display correctly. Can you walk me through the difference between the 5 and the 8 in:

Please Login or Register  to view this content.