Hello again Ossiemac:
a little knowledge can be a dangerous thing, eh?
Looking back, now I understand why "Not sure that I fully understand what
you are trying to achieve".
I appreciate you hanging in there with me. It is still thanks to your input
that this was resolved.
Til next time - have a good one

"OssieMac" wrote:

Hi BEEJAY,

Have not been able to reply quickly because I am probably on a different
time zone to you and also I have been working. (I only do voluntary work
these days but never the less working.)

Anyway, on "don't understand ".CurrentRegion"." Because you had used the
code in your initial post I assumed that you understood it. Your last post
suggests that you do not need it for your current project.

However, for explanation, CurrentRegion is the range which contains data and
is adjacent to the nominated cell/range. It includes all of the range up,
down, right and left until it comes to full blank rows and columns or it
comes to the top or left of the worksheet. Therefore the CurrentRegion of the
ActiveCell includes all that area containing data that surrounds the
activecell but remember that in each direction it must reach a full blank row
or blank column or the top or left of the worksheet.

UsedRange is all that area of the worksheet that has been used. It is
measured from the intersection of the first row that contains data and the
first column that contains data to the intersection of the last row that
contains data and the last column that contains data.

--
Regards,

OssieMac


"BEEJAY" wrote:

Greetings OssieMac:
Thanks for your continued efforts.
Obviously I don't understand ".CurrentRegion".

The following works as I need it to.
If you can advise of anything to make it "better" code, I'd appreciate it.

Sub NameRangeCC()

Dim myCell As Object
' Ensure "Pricing" Sheet is Active
Worksheets("Pricing").Activate
' Input box to select appropriate cell
Set myCell = Application.InputBox( _
Prompt:="Select Price List Sub-Total Cell, in Column G", Type:=8)
myCell.Select
' Name Active Cell (only)
ActiveWorkbook.Names.Add Name:="PriceSub", _
RefersToR1C1:=ActiveCell
' The next Cell location is not fixed location in relation to above.
' Therefore, Require the select process, as shown
' Input box to select next appropriate cell
Set myCell = Application.InputBox( _
Prompt:="Select Options Total Cell, in Column G", Type:=8)
myCell.Select
' The following ARE in "fixed" position, in relation to the cell just named

' Name Active Cell (only)
ActiveWorkbook.Names.Add Name:="OptionSub", _
RefersToR1C1:=ActiveCell

' The following ARE in "fixed" position, in relation to the cell just named
' Down One Cell, in Same Column
ActiveCell.Offset(1, 0).Select
ActiveWorkbook.Names.Add Name:="Price_Option_Sub", _
RefersToR1C1:=ActiveCell

' Down One, Left One
ActiveCell.Offset(1, -1).Select
ActiveWorkbook.Names.Add Name:="Disc_Factor", _
RefersToR1C1:=ActiveCell

' Right One
ActiveCell.Offset(0, 1).Select
ActiveWorkbook.Names.Add Name:="CostTotal", _
RefersToR1C1:=ActiveCell
etc.............................
End Sub

Thanks (in advance) again.


"OssieMac" wrote:

Hi again BEEJAY,

Since you have name the cell you can continue to use the named cell this
way. There are other ways also to save the cell but I won't confuse you with
multiple solutions at this point.

' Name Active Cell (only)
ActiveWorkbook.Names.Add Name:="OptionSub", _
RefersToR1C1:=ActiveCell

'SOMEHOW need to "set" the above cell for use by the rest of the code

' x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x

' Name Current Region based on Active Cell Position
' 1 Row Down, 0 Columns left
ActiveWorkbook.Names.Add Name:="Price_Option_Sub", _
RefersToR1C1:=Range("OptionSub").Offset(1, 0).CurrentRegion


--
Regards,

OssieMac