ProductSearch - Unhiding/Hiding Rows on Multiple Worksheets based on Column Value

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Hi Guys


    Im after a little bit of guidance in some VBA coding. My skills are quite limited... I basically learn bits that i need to know and im struggling with this even after hunting the wonderful web.


    Heres the setup.. I have a workbook... theres about 8 worksheets but only 3 are relevant for this. On the main tab “Worksheet 1” I have 5 drop down boxes (C9:C13) containing the same list of 150 products. For 2 of those products, I needed to be able to unhide and hide rows on the 3 sheets based on if either of those were selected. If Product 1 is selected in the Range then unhide the required rows, it if doesn't appear in the table, then hide. For each of the 2 products, if they do not appear in the C9:C13 range they should always be hidden.


    After some work I managed to come up with the attached code. It all worked as it should. This was fine when I only had 2 products that had specific rows to be unhidden.. then I needed to add a 3rd and the code grew massively (the version I've shared). Now I need to add another and my brain told me that this just isnt the right way to make something easily scalable if I had to add in any other products with specific rows. On the code ive highlighted in BLUE the specific rows for Product 1, RED is Product 2 and GREEN is 3.



    So I changed tack completely and went at it from another direction. What I then tried is, on each of the 3 worksheets, in Column A.. on the specific rows i need to unhide.. I have a formula that either populates with “UH” if it appears in the Product selection and “H” if it does not. I then tried my best to write a worksheet change on “Worksheet 1” to go and find the “UH” in Column A and unhide it... if its “H” then it should be hidden. It also needs to take into the account the other 2 worksheets that it also needs to apply to (“Worksheet 2”), etc. I've got myself so confused. The only content in column A on any worksheet, are the UH/H formulas on the specific rows to be included. I tried many different things that either do nothing or give me the waiting circle and then do nothing.


    So I guess this is a bit 2 fold... should i have stuck with my original idea but gone at the code in a different way (my skills are limited so i try to adapt what i know)... or was adding formulas to the rows in question and putting some code around that the best way to make this scalable?


    Please let me know if any further info is needed, id rather not upload the workbook as i would have to give it a good sanitise, but if needed im sure i can do something.


    Thanks in advance for any assistance for my brain.

    Apologies. I did what I thought was best rather than reading the rules. Corrected.


    I have now pasted the code from the word doc and removed it.


    Data on Multiple Sheets - Each of the worksheets represent completely different data, however, there are items on each of the worksheets that are specific to a certain "product". If that certain item is not in scope... there is no need to present the rows to the user... if that item is in scope... the user needs to have visibility of said row(s). Granted, it would have been much easier to manage on one sheet, but that isnt how the data is formatted. I am just trying to see if there is anything I can do with this setup and add some automation. With my limited skills... the code shown above does work and does what i need... but to add another "product" to that in the way its currently written would be a nightmare.


    Thanks

    As stated in my previous update... there is data contained in the workbook that I will need to cleanse before i can share that. It will take me a little time to complete as I have to juggle work, etc. As soon as I get the opportunity i will upload a workbook. Ta

    Hi again and thanks for the patience. I have stripped the workbook back to just the elements involved with the above decision. Would it be easier for me to simplify the current VBA code to make it more scalable, or is the other idea I mentioned using the Formulas and a bit of VBA the "right/best" way to achieve what I'm trying to do?


    On the attached I've tried to cover both options to show what I mean.


    Workbook For Ozgrid.xlsm


    Tracker 1 - The main sheet and where you make selection for "Product". On the example, I have already picked the 3 specific elements that drive the various hides/unhides in the script. On this sheet are rows which need to be hidden/unhidden.. as you scroll down you will notice those. As well as those rows being hard coded into the VBA script, I have also populated Column A for those specific rows. What that does is simply put the letters "UH" in the box if it appears in the selection boxes.. and "H" if it does not. I've copied that over to the other worksheets for the relevant rows. Hopefully it shows the 2 options ive discussed.


    Tracker 2 - Some rows to be hidden/unhidden for 1 item


    Tracker 3 - Some rows to be hidden/unhidden for 2 items


    So essentially... if I needed to add another item with specific rows to hide/unhide, using the current VBA method in the workbook.. it would grow to an unmanageable size. So should I be looking at changing that script to make it much easier to scale (where I would need some advice)... or is the 2nd idea with placing the formulas on the rows to be hidden/unhidden and having some kind of VBA script that comes along and hides anything with "H" in column A on those 3 worksheets or unhide them if "UH" the "best practice" here? I've tried many times to do both, they either do nothing or lockup the workbook in what seems like some never ending loop. I thought it best to seek some advice rather than running into the same wall each time.


    Please do let me know if that doesn't make sense or further info is needed. Thanks for your time.


    J

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login