255 Variable length limit?

There is a limit, and it is 255 characters.

Perhaps a loop like

Please Login or Register  to view this content.

Did you try the code that I posted?
With the .ReferToRange property of a Name, there is no reason to set a variable to a Name's .RefersTo string.

What length over 255 are you talking about?

My code doesn't use any strings so length doesn't apply to any of the part of it.

I think that you are talking about the character limit for Names. My testing (active sheet name is Sheet1) works when Size = 18, Len(Names("myRange").RefersTo)=225, but fails with size = 19, Len(..)=238

Please Login or Register  to view this content.

I wonder if you need a different approach to your problem. If you are creating useless Names.....

I see two ways to approach this
1) Avoid using names for this. You could use a UDF that returns the Range object instead of a name.

2) Sub-Naming. A workaround for long RefersTo strings is to define two (or more) names and then combine them in the definition of your final name

Name: myNamedRange RefersTo: =Name1,Name2

In VBA

Please Login or Register  to view this content.

Thanks again for the help, and I created a short example file to help demonstrate.

The end goal is here is for the user to select the appropriate workpapers (worksheet) and be able to clear the ranges (essentially the shaded cells). The sample just shows a questionnaire, but the actual workbook has much more then that with information throughout a worksheet they would want to clear to start over.

On a side note, I had tried a different approach on this looping the cells for the interior color, however it seemed much to counterproductive looping all cells in all worksheets.

You'll see two buttons on the userform. If you press 'Yes', the code runs but nothing happens because the length of variable in the named range is to large. But the second button/attempt is looking a list of the named ranges values and then processing that.

But if you were to actually shorten the variables of the named range to only a couple of 'sections' of the shaded areas to something under 255 characters long then the cells would be cleared if you did press 'Yes'.

Hope that all makes sense, and perhaps I am making this alot harder then it should be.

I notice that the ranges that you want cleared are located regularly.

Starting at A18, each Area is 4 rows high and 13 columns wide

The next Area begins at A25, 7 rows below the first, and the pattern continues.
If that pattern continues in your actual form, then you could use code like this in your user form

Please Login or Register  to view this content.

Originally Posted by mikerickson

I notice that the ranges that you want cleared are located regularly.
Starting at A18, each Area is 4 rows high and 13 columns wide

Yes, that was just an example. The actual workbook has all different ranges the user would clear throughout each worksheet (I still need to create them though). I think the workbook file has about 70 different worksheets all with different areas the user would potential want to clear. I just used that 1 worksheet as an example and duplicated to show what I was trying to accomplish.

But do you there is a better way to try and clear various sections of a worksheet. This was the solution I could come up with to work around the character limit.

You can union ranges in names:

inputs1 Refers To =A1:A10

inputs2 Refers To =C1:D12

inputs3 Refers To =...

allInputs Refers To: =inputs1, inputs2, inputs3, ...

Then in VBA,

Please Login or Register  to view this content.

Try this.
I notice that there were the three areas in G10, 12 and 14.
All there rest of the answer areas started in column A AND were merged cells And that the merged cells that started in column A extended past column G


Can you set up your 70 sheets with these restrictions.
1) All answer fields involve Merged Cells
2) All answer fields intersect column G, even though they might not begin in that column.
2) no other merged cells on the sheet

If that can be done, set up your 70sheets,
and then run this macro

Please Login or Register  to view this content.

That will set up each answer field as its own named range (scoped to the workSHEET).

Then the Clear code would look like

Please Login or Register  to view this content.