Naming a range using a macro

[immdav]

I would like to name a column or unique numbers using a macro.
I tried simply recording a macro (I tried this both as a absolute and relative macro) selecting cell C2 and doing <CTRL> <shift> down arrow.
When I tested the macro out it always name a range of cells with the same length of cell as the spread sheet that I originally recorded the macro on. If the spreadsheet I recorded the macro on had 277 rows of data and I ran the macro on the sheet that has 300 rows of data the range it would pick is c2:c277 instead of c2:c300.

I do know a bit of VBA but a bit of a beginner in VBA.

 

[Yahya]

Try this code

Sub Test()
Dim LR As Long
LR = Range("c" & Rows.Count).End(xlUp).Row
ActiveWorkbook.Names.Add Name:="MyRng", RefersTo:=Range("c2:c" & LR)
End Sub

 

[wigi]

Or:

Sub Macro1()
    ActiveWorkbook.Names.Add Name:="MyRng", RefersTo:=Range([C2], [C2].End(xlDown))
End Sub

 

[mikerickson]

There's no need for a macro. A dynamic named range will work.

Name: myRange
RefersTo: =INDEX(Sheet1!$C:$C,2,1):INDEX(Sheet1!$C:$C, MATCH("zzzz", Sheet1!$C:$C), 1)


if C:C contains numbers rather than text, substitute 1E+308 for "zzzz".

 

[immdav]

Thanks for all your help!