returning the first name on the list assigned to a particular rang
 

Hi,

my problem is as follows:

There is a piece of code in MS VB Help that says:

"Range Objects
Although a Range object can have more than one name, theres no Names
collection for the Range object. Use Name with a Range object to return the
first name from the list of names (sorted alphabetically) assigned to the
range. The following example sets the Visible property for the first name
assigned to cells A1:B1 on worksheet one.

Worksheets(1).Range("a1:b1").Name.Visible = False"

Now, the problem is that it doesn't work, at least for me. I have defined a
name "Whole" with reference: "=Sheet1!$1:$65536", and a macro:

Sub Wholemacro()
Worksheets("sheet1").Range("a1").Name.Visible = False
End Sub

Nevertheless, all I ever get when trying to run the code is "Run-time error
'1004': Application-defined or object-defined error". The macro code is as
follows:

Please help!

Thanks in advance

returning the first name on the list assigned to a particular rang
 

I'm not sure what you're trying to do but if it's to make a name hidden this
is the way:

Names("MyName").Visible = False

--
Jim
"Kimmo Kallio" wrote in message
...
| Hi,
|
| my problem is as follows:
|
| There is a piece of code in MS VB Help that says:
|
| "Range Objects
| Although a Range object can have more than one name, there's no Names
| collection for the Range object. Use Name with a Range object to return
the
| first name from the list of names (sorted alphabetically) assigned to the
| range. The following example sets the Visible property for the first name
| assigned to cells A1:B1 on worksheet one.
|
| Worksheets(1).Range("a1:b1").Name.Visible = False"
|
| Now, the problem is that it doesn't work, at least for me. I have defined
a
| name "Whole" with reference: "=Sheet1!$1:$65536", and a macro:
|
| Sub Wholemacro()
| Worksheets("sheet1").Range("a1").Name.Visible = False
| End Sub
|
| Nevertheless, all I ever get when trying to run the code is "Run-time
error
| '1004': Application-defined or object-defined error". The macro code is as
| follows:
|
| Please help!
|
| Thanks in advance
|
|

returning the first name on the list assigned to a particular rang
 

Kimmo,

I'm not sure where you got that code from but the Range object doesn't have a visible property, nor
a name property for cells that are merely part of a named range. And if you want to use the name for
a range, you need to have the entire range. If you want to do what you seem to want to do, you
would need to loop through the names collection to find a name that includes the cell of interest,
and then hide the row:

Sub TryNow()
Dim myR As Range
Dim myN As Name

Set myR = Worksheets(1).Range("A1")

For Each myN In ActiveWorkbook.Names
If Not Intersect(myR, Range(myN.Name)) Is Nothing Then
MsgBox "A1 is part of " & myN.Name
Range(myN.Name).EntireRow.Hidden = True
Exit Sub
End If
Next myN
'If you want to restrict your search to the activesheet's names:
'For Each myN In ActiveSheet.Names
'If Not Intersect(myR, Range(myN.Name)) Is Nothing Then
'MsgBox "A1 is part of " & myN.Name
'End If
'Next myN
End Sub


HTH,
Bernie
MS Excel MVP


"Kimmo Kallio" wrote in message
...
Hi,

my problem is as follows:

There is a piece of code in MS VB Help that says:

"Range Objects
Although a Range object can have more than one name, there's no Names
collection for the Range object. Use Name with a Range object to return the
first name from the list of names (sorted alphabetically) assigned to the
range. The following example sets the Visible property for the first name
assigned to cells A1:B1 on worksheet one.

Worksheets(1).Range("a1:b1").Name.Visible = False"

Now, the problem is that it doesn't work, at least for me. I have defined a
name "Whole" with reference: "=Sheet1!$1:$65536", and a macro:

Sub Wholemacro()
Worksheets("sheet1").Range("a1").Name.Visible = False
End Sub

Nevertheless, all I ever get when trying to run the code is "Run-time error
'1004': Application-defined or object-defined error". The macro code is as
follows:

Please help!

Thanks in advance