PDA

View Full Version : Sleeper: Naming a range and subdividing it



Keith_H
11-24-2004, 04:10 PM
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
:confused:

Regards

Keith

Jacob Hilderbrand
11-24-2004, 05:27 PM
Try something like this:

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

Keith_H
11-26-2004, 05:04 AM
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
11-26-2004, 05:50 AM
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
11-26-2004, 08:36 AM
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
11-26-2004, 10:02 AM
seems more intuitive than using the Names collection.

And I agree 100 % with You as it more understandable :yes

Kind regards,
Dennis

Mike_R
11-28-2004, 07:18 AM
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.