Expand named range using VBA

I am trying to create a macro that will add a value to an existing named range, sort the name alphabetically and then expand the named range to include the new value.

I have managed to get the data into the list, and sort the list alphabetically. The only part I am struggling with is expanding the named range. Can anybody help?
Here is the code so far:

Please Login or Register  to view this content.

Originally Posted by MarvinP

Hi ormerods,

Without reading your code this looks like a job for "Dynamic Named Range" or "DNRs". They expand based on how much data there is automatically so you can write your code using the name and don't need to worry about how much data there is:

Read up at:

http://www.ozgrid.com/Excel/DynamicRanges.htm
http://www.vertex42.com/ExcelArticle...ed-ranges.html
or my favorite at:
http://www.bettersolutions.com/excel...G820716330.htm

Thanks for that, is there an issue with using this function and the INDIRECT function? I cannot seem to get it working with data validation drop down lists?

Originally Posted by Andy Pope

change the code at the end of the routine

Please Login or Register  to view this content.

Thanks but this does not expand the named range it remains the same?

Thanks Andy I will try this on Monday. Had a read through your code and it makes no reference to the "Data" sheet where the fault is copied to, is this correct?

Thanks that works perfectly