Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,355
Default Range Name Limitations - Max Refers To Length?

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

  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Range Name Limitations - Max Refers To Length?

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!

Formula:
// Sample macro code
Sub ExampleMacro()
    
' Your code here
End Sub 
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,344
Default Range Name Limitations - Max Refers To Length?

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

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Range Name Limitations - Max Refers To Length?

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
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,355
Default Range Name Limitations - Max Refers To Length?

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



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,355
Default Range Name Limitations - Max Refers To Length?

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Validation length, Range length I think I need to rephrase the question Excel Discussion (Misc queries) 5 September 17th 07 06:29 AM
Named Range Limitations Bagia Excel Discussion (Misc queries) 8 May 2nd 07 09:11 PM
countif argument for 3 occurences of which 1 refers to a range Andy Excel Discussion (Misc queries) 2 September 7th 05 03:19 PM
toggling which worksheet a named range refers to Loftus Excel Discussion (Misc queries) 0 March 30th 05 12:05 AM
named range refers to: in a chart Spencer Hutton Excel Discussion (Misc queries) 1 December 14th 04 10:15 PM


All times are GMT +1. The time now is 09:29 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"