I'm trying to modify a bunch of named ranges and I think I'm hitting a
limitation on the maximum length for the Refers to field. Does anyone know
what that is? The length of what I have is between 69 and 110

Thanks,
Barb Reinhardt

Hi Barb,

Yes, there is a limitation on the maximum length for the Refers to field in named ranges in Excel. The maximum length is 255 characters. If you are hitting this limitation, you will need to shorten the range name or find a way to simplify the range reference.

Here are a few tips to help you work within this limitation:

1. Use shorter range names: Try to use shorter, more concise range names that still accurately describe the data in the range.
2. Use cell references: Instead of using a long range reference, try using cell references. For example, instead of using "Sheet1!A1:Z100", use "Sheet1!A1" and "Sheet1!Z100" separately.
3. Use named formulas: If you need to reference a complex formula or calculation, consider creating a named formula instead of a named range. Named formulas can be longer than named ranges.

Hope this helps!

Hi Barb,

The limit is 255 in 2003 or earlier, I haven't check 2007 but I don't think
it changed.

--
Cheers,
Shane Devenshire
Microsoft Excel MVP

"Barb Reinhardt" wrote:

I'm trying to modify a bunch of named ranges and I think I'm hitting a
limitation on the maximum length for the Refers to field. Does anyone know
what that is? The length of what I have is between 69 and 110

Thanks,
Barb Reinhardt

I thought that the limit was 256 characters, but I must be wrong if you're
failing before you exceed 110 characters.

It may be better to create the range and then name it instead of using a lot of
addresses.

Dim myRng as range
with activesheet
set myrng = union(.range("a1"), .range("c2:x99"), .... .range("iv99"))
end with

myrng.name = "HiThere"

or if it's a worksheet level name.

with myrng
.name = "'" & .parent.name & "'!HiThere"
end with

Barb Reinhardt wrote:

I'm trying to modify a bunch of named ranges and I think I'm hitting a
limitation on the maximum length for the Refers to field. Does anyone know
what that is? The length of what I have is between 69 and 110

Thanks,
Barb Reinhardt

--

Dave Peterson

It could also be that my Offset function isn't set up properly. Will have
to look at that further.
Thanks,
Barb Reinhardt



"ShaneDevenshire" wrote:

Hi Barb,

The limit is 255 in 2003 or earlier, I haven't check 2007 but I don't think
it changed.

--
Cheers,
Shane Devenshire
Microsoft Excel MVP

"Barb Reinhardt" wrote:

I'm trying to modify a bunch of named ranges and I think I'm hitting a
limitation on the maximum length for the Refers to field. Does anyone know
what that is? The length of what I have is between 69 and 110

Thanks,
Barb Reinhardt

It wasn't the refers to length that was the problem. It was my reference to
the worksheet name in my code that was the problem. Thanks for your help.

Barb Reinhardt



"ShaneDevenshire" wrote:

Hi Barb,

The limit is 255 in 2003 or earlier, I haven't check 2007 but I don't think
it changed.

--
Cheers,
Shane Devenshire
Microsoft Excel MVP

"Barb Reinhardt" wrote:

I'm trying to modify a bunch of named ranges and I think I'm hitting a
limitation on the maximum length for the Refers to field. Does anyone know
what that is? The length of what I have is between 69 and 110

Thanks,
Barb Reinhardt