Automatically define Range

Have you looked into dynamic named ranges? They allow for the growth of data. If dynamic named ranges do not seem to suit your needs, please upload a sample workbook with a more detailed explanation on your intent and purpose.

I've just had a look into it then, but how would i use this if I had Multiple Dynamic Ranges that would start and finish one after the other in column A?

Here is an improved version of your code:

Please Login or Register  to view this content.

The code to cycle through Column A and add named ranges, based upon border, is:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Originally Posted by jordan2322

Is there a method to organise all named ranges into a given order set out on a different worksheet?

I am not sure what you mean. Do you want to reproduce the data on another sheet, sorted by Name? Or just list the names in alphabetical order?

Yes i want to reproduce the data onto another sheet sorted in the following order:

Dave Wilkinson
Ian Wood
Mick Attkinson
Shane Cox
Brett Jones
Lee Kelly
Steve Kowal
Scott Paine
Andrew Richardson
Gary Nelson
Peter Baxter
Bryan Harris
Jeff Percival
Lance Houghton
Warren Gardner

Although this is the same order as what is currently there I will eventually be using the same code on another wookbook which will have different names to this one.

How is this list sorted? It is not alphabetical. For a custom sort, you would need some sort of helper information that designates the order.

You could try "Picture Links" that reference the named ranges.
http://blog.drscottfranklin.net/2008...-picture-link/

Couldnt I do something like this:

Please Login or Register  to view this content.

I've created a new worksheet titled "manifest" which contains the list of names previously posted above
I've also created a new worksheet titled "TempSH" which i'll paste the ranges into order on, then i'll delete the original worksheet. Will this method work and what do i need to change in the code to achieve this?

Solved.

Please Login or Register  to view this content.

Using a 'Manifest' sheet with the desired order i wish to use the ranges are found and pasted into order.

thanks for your excellent help Whizbang!!