Sleeper: Naming a range and subdividing it

[Keith_H]

Hi to all on the forum.

This is the issue I am trying to resolve. I have a report (standard format, but the length varies). Firstly I want to select the active area of the spreadsheet and name it. Secondly I want to divide the active area into its consitiuent elements. Thirdly, where necessary, divide into further mini-groupings.

I've been able to get the first part done by the use of some "borrowed" code.

Sub Method1()
LastRow = Cells(65536, 1).End(xlUp).Row
LastCol = Cells(1, 10).End(xlToLeft).Column
' the report width is never more than 10 columns
MyArea = "='" & ActiveSheet.Name & "'!R1C1:R" & LastRow & "C" & LastCol
ActiveWorkbook.Names.Add Name:="TheData", RefersToR1C1:=MyArea
End Sub

I've then created a second mini-macro to define some cells

Dim TheData As Range
    Cells.Find(What:="CONFIGURATION").Activate
    ActiveCell.Name = "Config"
    Cells.Find(What:="COMPONENT DETAIL - PURCHASE AND ONE TIME CHARGE").Activate
    ActiveCell.Name = "Component_detail"
    Cells.Find(What:="GROSS PROFIT - PURCHASE and ONE TIME CHARGE").Activate
    ActiveCell.Name = "Gross_profit"
    Cells.Find(What:="SOLUTION SUMMARY").Activate
    ActiveCell.Name = "Solution_summary"
    Cells.Find(What:="COMPONENT DETAIL - METRICS").Activate
    ActiveCell.Offset(-4, 0).Select
    ActiveCell.Name = "Last_Deleg"
        'this will be the last cell in the deleg block
    Cells.Find(What:="FEATURE DETAILS - PURCHASE AND ONE TIME CHARGE").Activate
    ActiveCell.Offset(-4, 0).Select
    ActiveCell.Name = "Last_GP"
        'this will be the last cell in the GP block
        'last cell in Solution block has yet to be defined
        'last cell in Config block will be defined in a future macro
End Sub

'Although these have worked, I've tried to reference these individual cells.
'This is what I was trying to do yesterday, but to no avail.

Range("Gross_profit", "Last_GP").Select
GpFirstRow = Range("Gross_profit").Row
GpFirstCol= Range("Gross_profit").Column
GpLastRow = Range("Last_GP").Row
GpLastCol = Range("Last_GP").Column
GpTop = "R" & GpFirstRow & "C" & GpFirstCol
GpTail = "R" & GpLastRow & "C" & GpLastCol
GpArea = "='" & ActiveSheet.Name & "'!" & GpTop &  GpTail
ActiveWorkbook.Names.Add Name:="GPData", RefersToGpTop:-GpTail

The last line is flagged as a syntax error. I am fairly new to scripting in general; it seems to be something related to the RefersTo syntax.

Please can someone advise where I am failing


Regards

Keith

[Jacob Hilderbrand]

Try something like this:

ActiveWorkbook.Names.Add Name:="GPData", _
RefersTo:= Sheets("SheetName").Range(MyRange.Address)

[Keith_H]

Jacob, thanks for your input. I showed this to a colleague. The advice was to scrap the original script

(Range("Gross_profit", "Last_GP").Select ........ ActiveWorkbook.Names.Add Name:="GPData", RefersToGpTop:-GpTail )

and to use the following:

Range("Component_detail", "Last_Deleg").Select
    ActiveWorkbook.Names.Add Name:="Deleg", RefersToR1C1:=Selection
    Range("Gross_profit", "Last_GP").Select
    ActiveWorkbook.Names.Add Name:="GP", RefersTo:=Selection

I was interested to see that it is possible to omit the R1C1 element.

Thanks for helping to resolve my problem.

[mark007]

You could go one further as lose the selects:

ActiveWorkbook.Names.Add Name:="Deleg", RefersToR1C1:=Range("Component_detail", "Last_Deleg")
ActiveWorkbook.Names.Add Name:="GP", RefersTo:=Range("Gross_profit", "Last_GP")

[Juan Pablo Gonz?lez]

I find it *a lot* easier to name ranges like

Range("A1:C100").Name = "MyName1"

or

Range("MyOtherName", "OtherName2").Name = "MyName2"

seems more intuitive than using the Names collection.

[XL-Dennis]

seems more intuitive than using the Names collection.

And I agree 100 % with You as it more understandable

Kind regards,
Dennis

[Mike_R]

Easier to read, yes, but if Worksheet-Level names are being created I then prefer the Worksheet.Names collection in this case. I think it's better than being forced to use "Sheet1!MyRangeName" style for the Range Name.

Also, to retreive a Named range, whether a Workbook-level Name or Worksheet-level name, the Names collection can be easier to read, as there is really no such thing as Workbook.Range("RangeName"), while there is a Workbook.Names("RangeName") capability.