Limit replication of coding inside spreadsheet

Hi all, I'll start with the good news, in that I have managed to get all the VBA to work just how I want it to. My problem is that now every tie I need to edit the sheet or moving things round/add rows etc I need to make onerous modifications to the code!!

Basically I wasn't smart enough when I set up my coding at the start.

I have 30 checkboxes which control different rows. They are all sequential rows or groups of rows but no consistency in the number of rows in each group. I.e. 1-6 in one group, 7-10 in another 11-25 in the next etc.

I have used the following code to achieve what I was hoping for on my spread sheet:

Please Login or Register  to view this content.

As you can see each sub has the row numbers written in twice, which need to be edited twice every time something changes on my sheet.

Is there a way that I can pre-define the groups of rows I want in one place, then get the code of each of the 30 checkboxes to refer to that?

I'm confident that it can be done, just not sure what the best way of doing it is? I do appreciate that I'll have to modify the code of each of the 30 checkboxes, but hopefully after this any modifications will be simpler!!!!


Most appreciated of any advice or help that can be offered
Regards

Paul

Code Tags Added
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(Because you are new to the forum, I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

Try this:

Please Login or Register  to view this content.

or this:

Please Login or Register  to view this content.

Set the ranges as Public outside the module. No need to set them inside the module.

Look at this link for an explanation.

http://www.ozgrid.com/forum/showthread.php?t=95488

You have set the range without a reference to any column.

Try changing it up to look like this:

Please Login or Register  to view this content.

It will not matter that you indicate a column since you are selecting the entire row to hide

Let me do some testing and I will get back to you.

Ok. My bad. You can't set the variable range as a constant. Instead here is a work around

Please Login or Register  to view this content.

You will have to define (Dim) each of your ranges above the function
Create a Getrng1, Getrng2, etc for each of your ranges

Then in your Sub you can set each of the ranges so that you will only have to reset the GetrngX when you need to make a range change and not the Sub itself.

Paul

How are the groups defined/identified?

Also, where are the checkboxes in location to the groups?