VBA code to amend named range

Hi All,

Sure this will be a simple query but I can't get my code to work. I'm not great at coding (self taught) but can find my own way normally by recording and Googling! I have a named range ("SalesPeople") that I want to alter once a new sales person is added to the data. My code is currently thus:-

ActiveWorkbook.Names("Salespeople").Refers to R1C1 = "=DATA!R1C5:R8C5

Effectively my current list of sales people populate E1:E8 on the DATA worksheet. My macro currently makes a number of changes to the workbook to incorporate this new salesperson however I need the above code to change the named range to incorporate the new salesperson whose name is sat in E9. I want the code to essentially rename the range to E1: end of selection ie, E9. Next time a new sales person starts the range would rename to E1:E10 etc etc. Not sure if I need to count the number of cells or maybe use some form of Selection.End(xlDown)?

The named range is used for Data Validation when assigning a sale to the relevant sales person by the way.

Any help that could be afforded to me would be most appreciated!

Regards
Paul

** EDIT - Forgot to mention file is currently 5MB so I can't post to the thread unfortunately! **

You can use the offset formula in the named range instead of VBA to create a named range that dynamically grows or shrinks as needed.

http://www.excel-easy.com/examples/d...med-range.html
http://www.contextures.com/xlNames01.html


but if you want to keep the VBA:

ActiveWorkbook.Names("Salespeople").Delete
ActiveWorkbook.Names.Add "SalesPeople", RefersTo:="=DATA!$B$3:$B$15"

or more complete:

 'Rename Named Range
    Dim sht As Worksheet
    Dim LastRow As Long
    
    Set sht = ThisWorkbook.Worksheets("DATA")
    
    'Ctrl + Shift + End
    LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
        ActiveWorkbook.Names("SalesPerson").Delete
            Range("A2:A" & LastRow).Name = "SalesPerson"
            
    Range("SalesPerson").Select