Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 | | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditionally format a cell using a list of data defined in a rang | Excel Worksheet Functions | |||
how to list all active macros and their assigned keystrokes? | Excel Worksheet Functions | |||
Returning looking up all values in a list | Excel Worksheet Functions | |||
Returning next value from a list | Excel Worksheet Functions | |||
Returning list values | Excel Worksheet Functions |