+ Reply to Thread
Results 1 to 18 of 18

How to create a macros that will name a range taking the value of a specific cell?

  1. #1
    Forum Contributor
    Join Date
    01-20-2011
    Location
    liverpool
    MS-Off Ver
    Excel 2016
    Posts
    196

    How to create a macros that will name a range taking the value of a specific cell?

    Hello friends,
    I have an xls file with data. I named ranges following that rule:
    range A2:A1000 =nameA (where nameA is the content of cell A1)
    range B2:B1000=nameB (where name B is the content of a cell B1)

    In the futue the file can grow and user can add himself additional data in column C.
    I would like to create a macros which will result in:
    1. deletion of the current range names used in the sheet
    2. creation of new range name based on the following rule: Range X2:X1000=name that refers to the content of cellX1. X could be any column that has a data. Lenght of data could less or longer that 1000 rows.

    Thanks in advance!!!

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: How to create a macros that will name a range taking the value of a specific cell?

    Try this:

    Please Login or Register  to view this content.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Contributor
    Join Date
    01-20-2011
    Location
    liverpool
    MS-Off Ver
    Excel 2016
    Posts
    196

    Re: How to create a macros that will name a range taking the value of a specific cell?

    Dear Alain,
    I have tested your proposal.
    It works but I need some small adjustment:
    when macros is executed I do not want any text box to be opened. Just macros needs to delete current range names on that sheet and to create new one based on sheet width nd length.
    Would you be so kind to adjust the code?
    Thanks.

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: How to create a macros that will name a range taking the value of a specific cell?

    Let me make sure I understand. Delete the current range names. If there are two columns with data, then two range names. If there are three columns of data, then three range names. Range names are based upon the values in row 1. Is this correct?

    Alan

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: How to create a macros that will name a range taking the value of a specific cell?

    If I understood correctly as noted above, then the following should do the trick.
    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    01-20-2011
    Location
    liverpool
    MS-Off Ver
    Excel 2016
    Posts
    196

    Re: How to create a macros that will name a range taking the value of a specific cell?

    hi Alain,
    the code correctly identifies new column and correctly defines the name of the range.
    but there is a problem with calculation of the "lenght"of the range. I did not mentioned that each column could have different number of records.
    So in fact this macros works only when the last column is the longest one. If that is not true then I am missing some entries from columns that are longer than the last one.

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: How to create a macros that will name a range taking the value of a specific cell?

    erkamu

    My apologies. It was late last night when I wrote the code. I placed a last column variable in the code when it should have been current column. Try this new code out.
    Please Login or Register  to view this content.

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,084

    Re: How to create a macros that will name a range taking the value of a specific cell?

    @Alan: you might want to ditch the delete loop. While it works in this instance for deleting and re-creating the Named Ranges for the columns, it will also delete any other Named Ranges that exist in the workbook.

    Maybe instead:

    Please Login or Register  to view this content.

    Setting DisplayAlerts = False copes with the scenario when the length of the range changes.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  9. #9
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: How to create a macros that will name a range taking the value of a specific cell?

    Trevor,

    How would you handle a situation like the following:

    Range of B1:B30 is named Alan

    User changes the name to Jack and changes the range area to B1:B25

    You now have two ranges overlapping when you only want the second one. This is what I envisioned when the OP requested this. I do understand what you indicated about other ranges, however.

    Alan

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,084

    Re: How to create a macros that will name a range taking the value of a specific cell?

    Hi Alan

    I would try to identify/segregate any other Named Ranges. For example, I tend to use "nr" as a prefix whenever I create a Named Range. If you use some sort of naming structure, you could check whether or not to include it in the delete loop.

    Then, provided the OP doesn't use the "secure" prefix, you can safely delete anything else.

    Regards, TMS

  11. #11
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: How to create a macros that will name a range taking the value of a specific cell?

    Trevor;
    Excellent suggestion.

    Alan

  12. #12
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,084

    Re: How to create a macros that will name a range taking the value of a specific cell?

    You're welcome.

  13. #13
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,084

    Re: How to create a macros that will name a range taking the value of a specific cell?

    For completeness, something like:

    Please Login or Register  to view this content.

    Regards, TMS

  14. #14
    Forum Contributor
    Join Date
    01-20-2011
    Location
    liverpool
    MS-Off Ver
    Excel 2016
    Posts
    196

    Re: How to create a macros that will name a range taking the value of a specific cell?

    Hello friends,
    The code is working on a new file. But when I transfer it to my file (has 10 sheets and other defined name ranges that are coming from other sheets )it does not work. Stops here(on x.delete line):
    For Each x In ActiveWorkbook.Names
    If LCase(Left(x.Name, 2)) <> "nr" Then
    x.delete
    and gives the following error:
    “Run-time error 1004:
    the name that you entered is not valid.
    Reasons for this can include:
    1.the name does not begin with a letter or an underscore
    2.the name contains a space or other invalid characters
    3.the name conflicts with an excel built-in name or the name of another object in the workbook"
    1 and 2 are not valid for the 3-rd option I did some checks and found out that other name ranges coming from other sheets probably are preventing macros to delete name ranges from the active sheet.
    How we can we modify the macros so it is deleting the name ranges not from the whole xls but only from active sheet?

  15. #15
    Forum Contributor
    Join Date
    01-20-2011
    Location
    liverpool
    MS-Off Ver
    Excel 2016
    Posts
    196

    Re: How to create a macros that will name a range taking the value of a specific cell?

    ...And can you please explain what :
    For Each x In ActiveWorkbook.Names
    If LCase(Left(x.Name, 2)) <> "nr" Then
    x.delete

    is doing? especialy line no2?. From where "nr" is coming?

  16. #16
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: How to create a macros that will name a range taking the value of a specific cell?

    That evolved from discussion between Trevor and me in posts 8-13. If you have not named any ranges with a prefix of "nr," as explained in those posts, then you could probable use the code I provided in post #7.

  17. #17
    Forum Contributor
    Join Date
    01-20-2011
    Location
    liverpool
    MS-Off Ver
    Excel 2016
    Posts
    196

    Re: How to create a macros that will name a range taking the value of a specific cell?

    Quote Originally Posted by TMS View Post
    For completeness, something like:

    Please Login or Register  to view this content.

    Regards, TMS
    Can someone explain me if I would like the code above to delete names which starts with "nr" how I should change the code?

    I have tried:
    Please Login or Register  to view this content.
    but it is not working it does not tecognize that name starts with "nr" :-(

  18. #18
    Forum Contributor
    Join Date
    01-20-2011
    Location
    liverpool
    MS-Off Ver
    Excel 2016
    Posts
    196

    Re: How to create a macros that will name a range taking the value of a specific cell?

    Quote Originally Posted by alansidman View Post
    erkamu

    My apologies. It was late last night when I wrote the code. I placed a last column variable in the code when it should have been current column. Try this new code out.
    Please Login or Register  to view this content.
    Another question: Is it possible to adjudt the code above in a way that will delete range names not in all workbook but only in the respective worksheet?

+ 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. Replies: 2
    Last Post: 05-31-2013, 12:01 AM
  2. How to get a quote sheet to save to a specific place without taking the macros with it!
    By gramomster in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-06-2013, 07:15 PM
  3. Replies: 4
    Last Post: 08-02-2012, 12:48 PM
  4. Replies: 1
    Last Post: 07-27-2012, 05:37 PM
  5. Taking data from an Input box and putting it in the first empty cell in a range
    By Benisato in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-27-2012, 12:03 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