How to use macro to create a name

[rickyckc]

Hi all,

I have a workbook call Sales.xls

In this workbook, there is a sheet call Sales Staff. In column C, there is a sales person name list. In C2, I want it to be blank. From C3 down-wards, are names of sales person. This name list are varied all the time, i.e. could be 10 people (C2:C12), could be 23 people (C2:25), etc. How to use macro to create a name for this (un-fixed) list ?

Currently, what I have is this, which is done by manually selection...

ActiveWorkbook.Names.Add Name:="SalesStaff", RefersToR1C1:= "='Sales Staff'!R2C3:R7C3"

Please help. Thanks.

Best Regards,
Ricky

 

[roentgen]

For a simple solution try:

Range("c3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.name = "SalesStaff"

This ignores c2 which you want blank and just names the list of staff names.
Each time this code runs, the full list is named irrespective of the number of entries.
Note that the list works down to the first blank, so there cannot be gaps in the list.

 

[Scott Huish]

No need to Select:

Sub test()
Range("C3:C" & Range("C3").End(xlDown).Row).Name = "SalesStaff"
End Sub