Dynamic named range change

I've created a small database of several countries with many corresponding cities which can be found on the worksheet Lists. Some countries have one city in the list and some have several.

I have a named range in Excel called CityList.
I want to dynamically change the range of this named range so that it starts at the first city that corresponds to the selected country and ends with the last city for the same selected country.

I've created some VBA code that effectively identifies the starting and ending row required.
I've also created code that redefined the list range.

The problem is the list range is hard coded not dynamic to react to any variables.

I'd like some help defining the syntax needed to make the hard coding variable.

What should happen is in the worksheet 'Main', you can select a country from the dropdown in A6 to A10. The corresponding cell in B has a dropdown referring to the range CityList. This should give you only the cities from that country.

I've got some data in column H that I was using to ensure my variables were being filled with the correct data which they seem to be. That part will be deleted when final coding is correct.


Sidenote: You may notice the dropdown in column A has duplicates for countries with more than one named city. My secondary problem will be to reduce that to unique values. If you want a crack at that problem I'm open to suggestions there too. If the answer is more VBA based than helper column based that would be ideal but any suggestion is good.

Declare First and Last as strings
Make them equal the address of the .Find cell offset by 1 column
and then this should work

Since you're using Office 365 I'd hope worksheet functions such as FILTER and UNIQUE are available to you.
If so I've used these functions on the Lists sheet.
The Main sheet's Worsheet_SelectionChange handler updates the value of D1 in the Lists sheet which then creates 2 lists; one a unique list of countries, the other a list of towns, which the code gives names to to use as cell Validation names to use on the Main sheet.
I've also converted the data in the first 2 columns of the Lists sheet to a table so that you can add to it without worrying about updating any named ranges sizes.

Hopefully this will deal with your main requirement and your side note.

What I haven't tried is using the formulae in E2 and G2 of the Lists sheet directly into the data validation cells.