Thread: Create Names Automacilly in a column

Hi,

I need to automaticaly create names for rows, ie: in column B, I need to create names L1, L2, L3, etc, starting from B21, and only going down as far as the rows that are being used.

I need a macro activated from a button, that will automatically start naming at B21, and go down as far as rows are being used, each time the worksheet is used, the amount of rows will differ.

Is this possible ?

I am ok with the button and record a macro, but I do not know what to record.

Many thanks.

Hi Grindlay,


What I am able to understand from your post that you want to automatically create name starting from column B row 21 (i.e., B21) which will cover all the entries in that row .. and then when you open this workbook again .. you need another name (L2) to cover B22 ..... to cover all entries in that row (i.e, 22) and so on. Let me know if I am correct in my understanding.

Regards,
DILIPandey

<click on below 'star' if this helps>

Ok... if the data will be in column B only, as you just shared in your post, you can use below formula to define a dynamic name which will dynamically / automatically considers the varying ranges in column B :-

=OFFSET(Sheet1!$B$21,0,0,COUNTA(Sheet1!$B$21:$B$1048576),1)

sample file attached to facilitate further explanation, if still not clear post your file. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

As per your words, B21-B40 may produce 20 names: L1->L20? Is it comfortable?
You may be thinking of dynamic name, which can cover all used cells in range automatically. Try this:
Name: Name1
Refer to: =OFFSET($B$21,,,COUNTA($B$21:$B$65000))
Is this your issue?

If "name" is unfamiliar to you, try this to create "name":
Ctrl-F3
Name: i.e: ABC
Refer to: = your expression.
OK

Ok... When I press the button it started populating the data in column C and E, starting from row 21.

Now do you want to create a name for the items in C or do you want to create a name for items in E or both ?

Regards,
DILIPandey

<click on below 'star' if this helps>

Ok.. you want to create a name for each row... but since you'll have only two entries per row, C21 & E21 and then C22 & E22 and so on.., So the name would be for only these two cells Or it would be C21:E21 (covering 3 cells C21, D21, E21)... ?

Regards,
DILIPandey

<click on below 'star' if this helps>

Maybe this will do what you want. Tag it onto your existing code.

Dim LCR As Long
LCR = Range("C" & Rows.Count).End(xlUp).Row
ActiveWorkbook.Names.Add _
    Name:="Row_" & LCR, _
    RefersToR1C1:="=Sheet1!R" & LCR

Regards, TMS