Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Lounger
    Join Date
    Jun 2002
    Location
    New Hampshire
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro for automatically naming ranges (Excel 2003 )

    Hi,

    I'm new to Excel macros (today!) and need some help. My objective is to assign individual range names to 1300+ rows in a worksheet. The numeric identifier (product #) for each row of info is in column A. I would like each range to have a name comprised of the letters nm, immediately followed by the product ID contained in column A. For example: the range name for the first row would be "nm1258", the second row would be "nm1262", etc. I realize that to automate this naming process the macro will need to use relative information so that it can "walk down" the spreadsheet one row at a time.

    I tried a recorded macro (below) that does not work because it uses abosulte addressing.
    --------------------------------------
    Sub name_range()
    '
    ' name_range Macro
    ' Macro recorded 9/20/2005 by Bill Gillan
    '
    ' Keyboard Shortcut: Ctrl+n
    '
    ActiveCell.FormulaR1C1 = "118"
    Range("A6:F6").Select
    ActiveWorkbook.Names.Add Name:="nm118", RefersToR1C1:="=MASTER!R6C1:R6C6"
    Range("A7").Select

    End Sub
    --------------------------------------------------------------------
    How can I fix this or write a new macro to automate my naming process. I know this is a lot to ask, but any help / guidance is greatly appreciated!

    Thanks,

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro for automatically naming ranges (Excel 2003 )

    Does this do what you want?

    <code>
    Public Sub NameRows()
    Dim I As Long, lLast As Long
    lLast = Range("A65536").End(xlUp).Row - 1
    For I = 5 To lLast
    ActiveWorkbook.Names.Add Name:="nm" & Range("A1").Offset(I, 0), _
    RefersTo:="A" & I + 1 & ":F" & I + 1
    Next I
    End Sub
    </code>
    Legare Coleman

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Macro for automatically naming ranges (Excel 2003 )

    Hi,
    Can I ask why you want every row named?
    If you want a non-VBA approach, you could insert a column before column A, enter the formula ="nm"&B6 in cell A6 and fill down as necessary. Then select cells A6 down to the end of column F, choose Insert-Name-Create and select Left Column. You can then delete column A if you wish.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro for automatically naming ranges (Excel 2003 )

    <hr>Can I ask why you want every row named?<hr>
    I suspect it is due to Hans' response to his <post#=519,079>post 519,079</post#>

    Steve

  5. #5
    Lounger
    Join Date
    Jun 2002
    Location
    New Hampshire
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro for automatically naming ranges (Excel 2

    Hi Legare,

    Thanks for this! I cannot get it to work and I'm much too inexperienced to quickly figure out why. I saved your macro into the worksheet and attempted to run it, but with no luck. I've attached a screen shot of the beginning of the spreasheet so that you can see what I'm working with. If you can provide any additional help, it's greatly appreciated.

    Thanks!

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Macro for automatically naming ranges (Excel 2003 )

    Thanks!
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Lounger
    Join Date
    Jun 2002
    Location
    New Hampshire
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro for automatically naming ranges (Excel 2

    Hi Rory,

    Thanks for your response. Every row has to be named because they're embedded as objects in word documents as absolute links. I need to make them all relative so that I can sort and delete rows from the spreadsheet without "breaking" the Word documents. Thanks again!

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Macro for automatically naming ranges (Excel 2

    Thanks - Steve has just pointed me to your reasons why.
    I see that your data actually starts in row 2 - from your original post both Legare and I assumed that it started in row 6. My way will work if you start in A2 rather than A6 or you can change Legare's code so that
    it reads:
    <pre>For I = 1 to lLast</pre>

    instead of
    <pre>For I = 5 to lLast</pre>

    and that should then work.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro for automatically naming ranges (Excel 2

    First, from that screen shot, it looks like the rows you want to name start in row 2. The code in your first message made it look like they started in row 6. The code below has been modified to make that change.

    <code>
    Public Sub NameRows()
    Dim I As Long, lLast As Long
    lLast = Range("A65536").End(xlUp).Row - 1
    For I = 1 To lLast
    ActiveWorkbook.Names.Add Name:="nm" & Range("A1").Offset(I, 0), _
    RefersTo:="A" & I + 1 & ":F" & I + 1
    Next I
    End Sub
    </code>

    Now, I am not sure what you mean by "I saved your macro into the worksheet." Code does not go into a worksheet, it goes into a module. In this case, into a normal module, not in the event module that is behind the worksheet (the module you get if you right click on the sheet tab and select "View code". To insert the code, you open the workbook and then press Alt+F11. That should take you to the Visual Basic Editor (VBE). Once there, you select "Module" from the Insert menu. This should insert a module into VBE where you can paste the code above. Just click in the editor window and paste the code there.

    To run the code, go back to the worksheet, and make sure that the sheet with the rows to be named is active since this macro works on the active sheet. Then select Macro from the Tools menu, and click on Macros in the fly-out menu. Click on NameRows in the list of macros and then click the Run button.
    Legare Coleman

  10. #10
    Lounger
    Join Date
    Jun 2002
    Location
    New Hampshire
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro for automatically naming ranges (Excel 2

    Hi Legare,

    First, I appreciate your generosity with your time and expertise - thank you again! I followed your detailed instructions carefully (three times) and had no luck with the macro. As far as I could tell, no range names were created in the spreadsheet. I've attached a VBA screen shot just to verify that I stored the macro correctly. It appeared in the Macros menu as you indicated it would . . . it just didn't seem to do anything.

    I realize I'm really taking advantage, but any additional help is greatly appreciated.

    Regards,

  11. #11
    Lounger
    Join Date
    Jun 2002
    Location
    New Hampshire
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro for automatically naming ranges (Excel 2

    Thanks rory,

    Trying to get Legare's macro to work. I appreciate your feedback.

    Regards,

  12. #12
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Macro for automatically naming ranges (Excel 2

    You have to then run the macro; in the VBE window menu (as in your screenshot) click on the "Play" icon (see graphic). You won't see much happen, but then look at Insert | Name | Define and see if the names have been created.

    Sorry, I'm probably confusing you by giving different instructions from Legare.
    -John ... I float in liquid gardens
    UTC -7ąDS

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro for automatically naming ranges (Excel 2

    Everything in the screenshot looks correct. Did you run the macro using either the method I explained in my previous message, or the method John described in his reply? I did create a test workbook and ran the macro and it did work. If you still can't get it to work, could you delete all of the data from your workbook other than what showed in your first screenshot, and upload that workbook in a reply so I can test the macro in your actual workbook? If the workbook is larger than 100kb after you delete most of the data, you will have to compress it into a .zip file before uploading.
    Legare Coleman

  14. #14
    Lounger
    Join Date
    Jun 2002
    Location
    New Hampshire
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro for automatically naming ranges (Excel 2

    Hi John,

    Thanks for the feedback. I did as you suggested and it worked as you indicated.

    Thanks again,

  15. #15
    Lounger
    Join Date
    Jun 2002
    Location
    New Hampshire
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro for automatically naming ranges (Excel 2

    Hi Legare,

    Again thanks for all of your help. I'm honestly not trying to be the problem that won't go away. I think I'm almost there. A remaining problem/issue is that I can see the named ranges when I click on Insert/Name/Define, but they do not show up in the worksheet "NAme Box" as my manually created ranges did (see attached). Also referencing them from the embbedded objects in Word also doesn't work (link error). What's baffling me is that my manually created named ranges worked perfectly, but these don't - even though the named range seems to be identical (start/end cells).

    What am I missing?

    Thanks,

Page 1 of 2 12 LastLast

Posting Permissions

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