Naming Range Between Top & Bottom Borders (2)

Hi Guys

I'm need some help in Naming ranges that have borders around them.

Ranges Start in Column A and extend to Column J.

The macro needs to look in Column A for a Top Border - set this as the top row for the range to be named, then find the 2nd Bottom Border in Column A - set that as the bottom Row for the named range.

The name for the range is found in the Top Row of Column A.

the values in these Cells are always Names i.e. "John Smith". (With the space)

I need the names for these ranges to be saved as "John_Smith" (Space replaced with _)

This should run everytime the current worksheet changes to a WS who's name is in ##-##-#### format

I've asked similiar questions in the past, however this is a different question and I feel warranted starting a new thread: http://www.excelforum.com/excel-prog...red-cells.html


regards

do you have an example sheet? is there any reason you have to work through this with a macro (and can't design the name to work with a formula that can expand to fit the table....)


-- Another point

If you do indeed need to use a macro, are you looking to define a static range like A3:j57 or are you looking to define a dynamic one like $A$3:INDEX($3:$100, counta($A$3:$A$100),10)

I need a macro to do this for me, the ranges are not static - the macro is run weekly on a new WB which has different ranges as previous week.

Please see attached WB

Any help with this one would be very much appreciated.

Thank you StevenM. One issue though - its not naming the last range when its found?
I think it has to do with the range that it is searching for the last used cell on..

I've tried using..

Please Login or Register  to view this content.

which finds the correct last Row

Regards