Macro for automatically naming ranges (Excel 2003 )

Topic

New Reply

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,

Reply | Quote

Replies

Does this do what you want?


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


Reply | Quote

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!

Reply | Quote

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.


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


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.

Reply | Quote

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,

Reply | Quote

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.

Reply | Quote

Hi John,

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

Thanks again,

Reply | Quote

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.

Reply | Quote

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,

Reply | Quote

edited: changed enter to entire

The name does not appear in the name box unless you select the entire range that has been named.
Are you selecting the whole range?

Chuck

Reply | Quote

Hi Chuck,

Yes, I’m pretty sure I was. Thanks for the response.

Regards,

Reply | Quote

Bill, in case the issue is that you wanted the entire row to have the range name (as implied in your original post), change the ‘RefersTo’ line of Legare’s post 519201 code to

RefersTo:= Range(“A1”).Offset(I, 0).EntireRow

(Ahh, untested, as I’m pressed for time.)

Reply | Quote

Hi John,

Sorry if I was unclear. The range for each row is A-F. Thanks for the feedback.

Regards,

Reply | Quote

My macro named columns A:F as your first message indicated you wanted. The image you included showed the active cell was G1, which would indicate that you did not select A1:F1.

Try the version of the macro below, I believe it will resolve the problem.


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:=Range(Range("A1").Offset(I, 0), Range("F1").Offset(I, 0))
Next I
End Sub


Reply | Quote

Hello Legare,

Columns A_F is the correct data range per row. I’ve attached a small portion of the spreadsheet in question that I believe will show you the issues I’m having without wasting too much more of your time. The first data row has a manually applied name range which shows up in the names box. The rest of the named ranges appear when you click on Insert/Name/Define. There are also many more “names” than data rows as I deleted the balance of the spreadsheet and I don’t know how to delete name ranges en-masse Thanks again for all of your your help!

Regards,

Reply | Quote

The names in that workbook were created using the first macro, not the macro in my last message. The attached workbook has all those names deleted, and new names created with the latest version of the macro. This workbook has the latest macro in it, and also contains a second macro that can be used to delete all of the names created by the first macro.

Reply | Quote

Legare,

Thanks so much for all of your help! Everything works great!

Best regards,

Reply | Quote

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.

Reply | Quote

[indent]

---

Can I ask why you want every row named?

---

[/indent]
I suspect it is due to Hans’ response to his post 519,079

Steve

Reply | Quote

Thanks!

Reply | Quote

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!

Reply | Quote

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:

For I = 1 to lLast

instead of

For I = 5 to lLast

and that should then work.

Reply | Quote

Thanks rory,

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

Regards,

Reply | Quote

Thank you to all who particpated in this thread! Your help was greatly appreciated!

Best regards,

Reply | Quote