Getting the last entry in a named range in excel with VBA-CodePudding

After copying a template sheet via vba I need to set two cells for start and end dates that use data validation referencing a sheet scoped named range for its formula. To do this I need the first and last entries for the named range.

To test the code before setting the cell data I have the following:

Debug.Print "Charts 1 Start " & Sh.Names("ProjectTemplateDates").RefersToRange(1, 1)

Debug.Print "Charts 1 End " & Sh.Names("ProjectTemplateDates").RefersToRange.End(xlDown).Value

The first Debug.Print gives me the correct first entry:

Charts 1 Start 01/09/2017

however the last entry code gives me:

Charts 1 End 100000

When I was expecting it to give me Charts 1 End 01/03/2023. Should I be doing something different to find the last entry in the named range?

CodePudding user response：

The End(xlDown) method returns the last non-empty cell in the specified direction. Try this and see if it gets what you're after.

Debug.Print "Charts 1 End " & Sh.Names("ProjectTemplateDates").RefersToRange.End(xlUp).Value

This will give you the last cell in the column of the named range, which should be the last entry of the named range.

CodePudding user response：

With sh.Names("ProjectTemplateDates").RefersToRange
    Debug.Print "Charts 1 Start " & .Cells(1, 1)
    
    Debug.Print "Charts 1 End " & .Cells(.Rows.Count   1, 1).End(xlUp).Value
End With