+ Reply to Thread
Results 1 to 26 of 26

The last Area of Named Range Issue

  1. #1
    Registered User
    Join Date
    07-20-2012
    Location
    King
    MS-Off Ver
    Excel 2016
    Posts
    75

    Question The last Area of Named Range Issue

    Hello,

    I have a named range called "Sh08InpRng", it has 18 areas.

    The 18 Addresses of the Range “Sh08InpRng” in The Name Manager is:

    Please Login or Register  to view this content.

    when i use Sheet8.Range("Sh08InpRng").Adress, it shows only the address of 17 areas, and the address of the last area doesn't show. (So, When i make .Clearcontents, it clears only the first 17 areas).


    Please Login or Register  to view this content.

    The result in Immediate Window:

    Please Login or Register  to view this content.

    The address of 18th area is $G$653:$AB$681, Doesn’t show in the immediate window.

    What is the issue?

    Thank you very much.

  2. #2
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016
    Posts
    660

    Re: The last Area of Named Range Issue

    If you skip the first range in de Named Range, it will show up:
    Please Login or Register  to view this content.
    I think there was a maximum of characters to be put in a Named Range, that might be the problem.

    Cheers
    Erwin
    I started learning VBA because I was lazy ...
    Still developing.... being more lazy...

  3. #3
    Registered User
    Join Date
    07-20-2012
    Location
    King
    MS-Off Ver
    Excel 2016
    Posts
    75

    Re: The last Area of Named Range Issue

    Thank you Erwin for your quick reply,

    The range designed to be 18 areas, so i can't skip any area.

  4. #4
    Registered User
    Join Date
    07-20-2012
    Location
    King
    MS-Off Ver
    Excel 2016
    Posts
    75

    Re: The last Area of Named Range Issue

    Also looping through the areas in the range may solve the issue, but, i doesn't like to use looping, i want to use single line method like Range("Sh08InpRng").address / Range ("Sh08InpRng").ClearContents.

    The Looping Method.

    Please Login or Register  to view this content.
    The Result

    Please Login or Register  to view this content.
    I think there is a solution for this issue.
    Last edited by exceere; 03-10-2020 at 07:25 AM.

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,557

    Re: The last Area of Named Range Issue

    As Erwin pointed out this is a character limit issue.

    That said I was able to use the range refer to populate, colour and clear without looping.

    If you want to see the full text of the range try

    Please Login or Register  to view this content.
    Cheers
    Andy
    www.andypope.info

  6. #6
    Registered User
    Join Date
    07-20-2012
    Location
    King
    MS-Off Ver
    Excel 2016
    Posts
    75

    Re: The last Area of Named Range Issue

    Thank you Andy Pope,

    But how can i use .RefersToLocal to clear the range?

    i tried Range(ThisWorkbook.Names("Sh08InpRng").RefersToLocal).ClearContents and i get error.

  7. #7
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,128

    Re: The last Area of Named Range Issue

    Administrative note

    Still using XL2003 as indicated in your profile?
    If not,perhaps update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to? Members tailor answers based on your Excel version.
    Thanks
    Be very, very careful using IFERROR ! It hides ALL errors which is not always what you want to get correct results

  8. #8
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,557

    Re: The last Area of Named Range Issue

    In my test I was able to use

    Please Login or Register  to view this content.
    If it errors for you then post example file so we can investigate

  9. #9
    Registered User
    Join Date
    07-20-2012
    Location
    King
    MS-Off Ver
    Excel 2016
    Posts
    75

    Re: The last Area of Named Range Issue

    I tried it but clears only 17 areas instead of 18. also .clearcontents

  10. #10
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,557

    Re: The last Area of Named Range Issue

    As I say, it works for me on my test.

    Your choices are post example so we can see the exact problem or use loops

  11. #11
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016
    Posts
    660

    Re: The last Area of Named Range Issue

    I was just a bit playing with this matter and found out this worked:
    Although the Debug.print statement is showing only 17 areas, (my) Excel is working with all 18 of them!
    Please Login or Register  to view this content.
    Of course there might be an easier way to do this...

    Cheers
    Erwin
    Attached Files Attached Files
    Last edited by Eastw00d; 03-11-2020 at 07:28 AM. Reason: additional information

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,640

    Re: The last Area of Named Range Issue

    This gives me
    HTML Code: 
    18           $G$653:$AB$681
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    07-20-2012
    Location
    King
    MS-Off Ver
    Excel 2016
    Posts
    75

    Re: The last Area of Named Range Issue

    Thank you Erwin,

    i am searching a single line solution and this is 100000000 line solution? (Just joking) Robbarb5.png

    i didn't understand your method. and how can use it to clear the full rage or get the full range address.

    thank you for your effort and time.
    Last edited by exceere; 03-11-2020 at 10:59 AM.

  14. #14
    Registered User
    Join Date
    07-20-2012
    Location
    King
    MS-Off Ver
    Excel 2016
    Posts
    75

    Re: The last Area of Named Range Issue

    Thank you, jindon

    How can i use this method to clear the full rage or get the full range address?

  15. #15
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,640

    Re: The last Area of Named Range Issue

    Untested but it should work...
    Please Login or Register  to view this content.

  16. #16
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016
    Posts
    660

    Re: The last Area of Named Range Issue

    Well, my solution started with the knowledge of "union", combining two sets of ranges
    from there I thought, what if I combine all the separate ranges into one and make that 1 named range.
    Without doubt my code can be written more compact, but like I said: I was just a little playing and still learning

    Cheers
    Erwin

    PS Jindon is right! Oneliner!
    Last edited by Eastw00d; 03-11-2020 at 11:09 AM. Reason: additional information

  17. #17
    Registered User
    Join Date
    07-20-2012
    Location
    King
    MS-Off Ver
    Excel 2016
    Posts
    75

    Re: The last Area of Named Range Issue

    Yes, With Clearing it works.

    But how can i get the full range address [Sh08InpRng].address to use it for clearing another identical sheet like:

    Please Login or Register  to view this content.
    Sheet9 doesn't have a defined Name range.

    Thak you very much.

  18. #18
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016
    Posts
    660

    Re: The last Area of Named Range Issue

    I think you need something to do like this:
    Please Login or Register  to view this content.
    Cheers
    Erwin

  19. #19
    Registered User
    Join Date
    07-20-2012
    Location
    King
    MS-Off Ver
    Excel 2016
    Posts
    75

    Re: The last Area of Named Range Issue

    i uses the code name of the sheet. Your method Worksheet("Sheet09") if the Sheet09 is the name of the sheet.

    sheet09 hete is the code name of the sheet.

    thank you very much.
    Last edited by exceere; 03-11-2020 at 12:08 PM.

  20. #20
    Valued Forum Contributor BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,028

    Re: The last Area of Named Range Issue

    The first issue was why - the answer because of string length limitation
    The second - how to clear - the answer is
    Please Login or Register  to view this content.
    Last edited by BMV; 03-11-2020 at 12:29 PM.

  21. #21
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016
    Posts
    660

    Re: The last Area of Named Range Issue

    Yes, that's because I don't have a sheet with codename Sheet09, after all, my systemlanguage is Dutch....
    My code then would be like this:
    Please Login or Register  to view this content.
    So I can only give my sheets another name.
    Cheers
    Erwin
    Last edited by Eastw00d; 03-11-2020 at 12:32 PM. Reason: additional information

  22. #22
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,640

    Re: The last Area of Named Range Issue

    Untested.
    Please Login or Register  to view this content.

  23. #23
    Registered User
    Join Date
    07-20-2012
    Location
    King
    MS-Off Ver
    Excel 2016
    Posts
    75

    Re: The last Area of Named Range Issue

    BMV Solution.
    ---------------------------------------------------------------
    Please Login or Register  to view this content.
    it Gives Run-Time Error '1004'

    also, I replaced Worksheet with sheets, also replaced it with sheet code name but gives the same error.
    ---------------------------------------------------------------Thank you


    Erwin Solution
    -----------------------------------------------------------------
    Please Login or Register  to view this content.
    Works but clears 17 areas instead of the 18.
    -----------------------------------------------------------------Thank you


    jindon Solution
    -----------------------------------------------------------------
    Please Login or Register  to view this content.
    I tested it and not work, it Gives Run-Time Error '1004'
    -----------------------------------------------------------------Thank you

  24. #24
    Valued Forum Contributor BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,028

    Re: The last Area of Named Range Issue

    it Gives Run-Time Error '1004'
    Strange, It's working I use it in the your code
    Please Login or Register  to view this content.
    Excel 2016 also

  25. #25
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,640

    Re: The last Area of Named Range Issue

    RefersTo in the name acts strange to me...
    This is a work around that I can think of.
    1) To a standard code module
    Please Login or Register  to view this content.
    2) To ThisWorkbook code module
    Please Login or Register  to view this content.
    3) You can call myAreas like
    Please Login or Register  to view this content.
    Last edited by jindon; 03-11-2020 at 11:15 PM.

  26. #26
    Registered User
    Join Date
    07-20-2012
    Location
    King
    MS-Off Ver
    Excel 2016
    Posts
    75

    Re: The last Area of Named Range Issue

    Thank you jindon and BMV,

    And thanks for every one share his mind.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] REF: Named Range Issue?
    By fredfarmer in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 03-13-2019, 02:22 PM
  2. [SOLVED] Named Range Issue?
    By fredfarmer in forum Excel General
    Replies: 11
    Last Post: 03-13-2019, 01:32 PM
  3. Replies: 1
    Last Post: 09-10-2018, 06:34 AM
  4. [SOLVED] Set Print Area to named range before saving as PDF and attaching in new email
    By PFDave in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-06-2016, 11:08 AM
  5. [SOLVED] Create a named range for each cell in a selected area
    By alice2011 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-13-2015, 07:06 AM
  6. [SOLVED] Named Range issue
    By SHUTTEHFACE in forum Excel General
    Replies: 3
    Last Post: 03-20-2015, 11:04 AM
  7. Referring to an area relative to a named range
    By petalred in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-07-2009, 05:36 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1