I have range name which is used as the rowsource in a userform.

I want to be able have the size of this range name be checked and changed when ever the form is called in order to ensure that only “live” rows are included in the list box selection. The code I have written checks the size of the range using the excel row and match functions to find the start and end rows and should then define the range name size using these limits:

Dim FirstCountryRow As String
Dim LastCountryRow As String

Set FirstCountryRow = Row("Startcountrylist")
Set LastCountryRow = FirstCountryRow + (Match("[Spare]", "FullCountryList", 0) - 2)

ActiveWorkbook = "CountryList"
ActiveWorkbook.Names.Add Name:="TempCountryList", RefersToR1C1:="=CountryList!R”&FirstCountryRow&”C1:R”&LastCountryRow&”C1"


However it’s not working and I’m having trouble debugging it so any help would be gratefully received.

i suggest you contact the office to have one of our vba specialsists talk to you