Looking for some help on hiding columns based on checkbox selection

Hi there.

I have a row with headers (Row 2) and each header is 3 columns in length with information about the header we can call "cities" for now. I have a userform where a user selects which cities he/she wants to view the data for. What i would like is to be able to hide all other cities that the user did not check. I currently have code that does what i need it to however, it is very slow in calculating and takes 10 seconds to load. Looking to hopefully find an alternative to shorten that calculation time.

Please Login or Register  to view this content.

Any help is appreciated

Any chance you could post some sample of your workbook? It would make it significantly easier to assist

Originally Posted by BigBas

Any chance you could post some sample of your workbook? It would make it significantly easier to assist

I ended up just figuring it out now as i was making a test book to post here. I do need some help on a similar idea though

Along with what i am trying to do here, i also have 3 checkboxes labeled "Complete", "In Process" and "Planned". Each one of these "cities" will have a heading underneath it in the same columns i have in the Union listed above (I, L, O, R, etc). I need a piece of code that can look through all the categories in Column A and filter what the status is on each city. So if i choose Buffalo and NYC in the userform and then select "Complete" in the userform, it should only show categories that are Complete in both those cities while hiding every other city and category.

attached example workbook.

To the best of my knowledge, your second request would not work. You can't filter individual ranges separately. So for example, if you are filtering by COMPLETE tasks, and New York has row 4 INCOMPLETE (which will hide it), but Buffalo shows Row 4 as Complete, there will be conflict. It gets tricky when trying to apply separate filters to separate ranges.

Also, if you are interested, I think you can tidy up your code by quite a bit if you use a listbox vs. multiple checkboxes. I've attached an example of what I mean. Click on Button 6 to show the form, and take a look at the code. The benefits, (1) much easier to follow/read code, (2) scalability; you can add additional cities, and as long as they follow the logic being used (3 columns per city), they will autopopulate into the form and follow the rules of the code, with needing to add a new checkbox and code for it.

Originally Posted by BigBas

To the best of my knowledge, your second request would not work. You can't filter individual ranges separately. So for example, if you are filtering by COMPLETE tasks, and New York has row 4 INCOMPLETE (which will hide it), but Buffalo shows Row 4 as Complete, there will be conflict. It gets tricky when trying to apply separate filters to separate ranges.

Also, if you are interested, I think you can tidy up your code by quite a bit if you use a listbox vs. multiple checkboxes. I've attached an example of what I mean. Click on Button 6 to show the form, and take a look at the code. The benefits, (1) much easier to follow/read code, (2) scalability; you can add additional cities, and as long as they follow the logic being used (3 columns per city), they will autopopulate into the form and follow the rules of the code, with needing to add a new checkbox and code for it.

I'll definitely take a look at this when i get home.

Another though - The first cell for each city is going to be an "Applicable to site?" Y/N answer. Just like with the last cell (In Process, Complete and Planned). Would there be an easy way to add in some code to do the following?

1. Search for a category
2. Select a Y or N checkbox to see the cities that are applicable to that category. All it would be doing is sorting the category (which i have the code for below, and hiding the cities that have a N in the first box. (The form auto fills Y or N in when i input a category)


Code for Category sorting:

Please Login or Register  to view this content.

Per the PM request

Works great!

Thanks again!

No problem!