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

Question

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?

user3598756 · Accepted Answer · 2023-01-23 13:27:55Z

1

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

edited Jan 23, 2023 at 13:27

answered Jan 23, 2023 at 13:17

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Automation Monkey Over a year ago

I think this would have worked if the data was in row format (something I forgot) so I ended up with the following working solution for the column based named range - Debug.Print "Charts 1 End" & .Cells(1, .Columns.Count).End(xlUp).Value

user3598756 Over a year ago

@AutomationMonkey: If data are on a single row, then use: Debug.Print "Charts 1 End" & .Cells(1, .Columns.Count + 1).End(xlToLeft).Value

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related