Dynamic Range Names using VBA 
Author Message
 Dynamic Range Names using VBA

Greetings.

I'm really stumped. I have an Excel worksheet set up with
a 3-column list of project codes, project descriptions,
client names. I've defined a range name for the entire
list (i.e., Project_Codes). This list will expand and
contract over time. I could manually re-define the range
name if the list expands or contracts, but a Visual Basic
macro could automate the procedure and integrate with
other macros I've written.

I recorded the following VB macro in Excel, which, of
course, is impractical (see below):

Sub UpdateRange_Project_Codes()

    Range("A5").Select
    Selection.CurrentRegion.Select
    ActiveWorkbook.Names.Add Name:="Project_Codes",
RefersToR1C1:= _
        "='Project Codes'!R5C1+'Project Codes'!R5C1:R13C4"

End Sub

How do I create a dynamic range name using VisualBasic
code, such that I can select the entire list and assign
the new range to the name "Project_Codes"?

Can anyone help?

Dirk Schmid
North/South Consultants Inc.
Winnipeg, Canada



Fri, 09 Dec 2005 16:04:31 GMT  
 Dynamic Range Names using VBA
Range("Project_Codes")(1).CurrentRegion.Name = "Project_Codes"

Regards,
Tom Ogilvy


Quote:
> Greetings.

> I'm really stumped. I have an Excel worksheet set up with
> a 3-column list of project codes, project descriptions,
> client names. I've defined a range name for the entire
> list (i.e., Project_Codes). This list will expand and
> contract over time. I could manually re-define the range
> name if the list expands or contracts, but a Visual Basic
> macro could automate the procedure and integrate with
> other macros I've written.

> I recorded the following VB macro in Excel, which, of
> course, is impractical (see below):

> Sub UpdateRange_Project_Codes()

>     Range("A5").Select
>     Selection.CurrentRegion.Select
>     ActiveWorkbook.Names.Add Name:="Project_Codes",
> RefersToR1C1:= _
>         "='Project Codes'!R5C1+'Project Codes'!R5C1:R13C4"

> End Sub

> How do I create a dynamic range name using VisualBasic
> code, such that I can select the entire list and assign
> the new range to the name "Project_Codes"?

> Can anyone help?

> Dirk Schmid
> North/South Consultants Inc.
> Winnipeg, Canada



Fri, 09 Dec 2005 16:19:41 GMT  
 Dynamic Range Names using VBA
Dirk try this

This code is totally dynamic for any lenght of data files.

Sub UpdateRange_Project_Codes()
    Range("A5").Select
    Selection.CurrentRegion.Select
    Selection.Name = "Project_Codes"
End sub

Regards,

Fernando Ortiz



Quote:
> Greetings.

> I'm really stumped. I have an Excel worksheet set up with
> a 3-column list of project codes, project descriptions,
> client names. I've defined a range name for the entire
> list (i.e., Project_Codes). This list will expand and
> contract over time. I could manually re-define the range
> name if the list expands or contracts, but a Visual Basic
> macro could automate the procedure and integrate with
> other macros I've written.

> I recorded the following VB macro in Excel, which, of
> course, is impractical (see below):

> Sub UpdateRange_Project_Codes()

>     Range("A5").Select
>     Selection.CurrentRegion.Select
>     ActiveWorkbook.Names.Add Name:="Project_Codes",
> RefersToR1C1:= _
>         "='Project Codes'!R5C1+'Project Codes'!R5C1:R13C4"

> End Sub

> How do I create a dynamic range name using VisualBasic
> code, such that I can select the entire list and assign
> the new range to the name "Project_Codes"?

> Can anyone help?

> Dirk Schmid
> North/South Consultants Inc.
> Winnipeg, Canada



Fri, 09 Dec 2005 16:27:50 GMT  
 Dynamic Range Names using VBA

Thank you for your help. The solution is simpler than I
thought! :-)

Quote:
>-----Original Message-----
>Dirk try this

>This code is totally dynamic for any lenght of data
files.

>Sub UpdateRange_Project_Codes()
>    Range("A5").Select
>    Selection.CurrentRegion.Select
>    Selection.Name = "Project_Codes"
>End sub

>Regards,

>Fernando Ortiz



>> Greetings.

>> I'm really stumped. I have an Excel worksheet set up
with
>> a 3-column list of project codes, project descriptions,
>> client names. I've defined a range name for the entire
>> list (i.e., Project_Codes). This list will expand and
>> contract over time. I could manually re-define the
range
>> name if the list expands or contracts, but a Visual
Basic
>> macro could automate the procedure and integrate with
>> other macros I've written.

>> I recorded the following VB macro in Excel, which, of
>> course, is impractical (see below):

>> Sub UpdateRange_Project_Codes()

>>     Range("A5").Select
>>     Selection.CurrentRegion.Select
>>     ActiveWorkbook.Names.Add Name:="Project_Codes",
>> RefersToR1C1:= _
>>         "='Project Codes'!R5C1+'Project Codes'!
R5C1:R13C4"

>> End Sub

>> How do I create a dynamic range name using VisualBasic
>> code, such that I can select the entire list and assign
>> the new range to the name "Project_Codes"?

>> Can anyone help?

>> Dirk Schmid
>> North/South Consultants Inc.
>> Winnipeg, Canada

>.



Fri, 09 Dec 2005 18:25:35 GMT  
 Dynamic Range Names using VBA

Quote:

> Thank you for your help. The solution is simpler than I
> thought! :-)

It still is: Range("A5").CurrentRegion.Name = "Project_Codes"

Alan Beban

Quote:

>>-----Original Message-----
>>Dirk try this

>>This code is totally dynamic for any lenght of data

> files.

>>Sub UpdateRange_Project_Codes()
>>   Range("A5").Select
>>   Selection.CurrentRegion.Select
>>   Selection.Name = "Project_Codes"
>>End sub

>>Regards,

>>Fernando Ortiz



>>>Greetings.

>>>I'm really stumped. I have an Excel worksheet set up

> with

>>>a 3-column list of project codes, project descriptions,
>>>client names. I've defined a range name for the entire
>>>list (i.e., Project_Codes). This list will expand and
>>>contract over time. I could manually re-define the

> range

>>>name if the list expands or contracts, but a Visual

> Basic

>>>macro could automate the procedure and integrate with
>>>other macros I've written.

>>>I recorded the following VB macro in Excel, which, of
>>>course, is impractical (see below):

>>>Sub UpdateRange_Project_Codes()

>>>    Range("A5").Select
>>>    Selection.CurrentRegion.Select
>>>    ActiveWorkbook.Names.Add Name:="Project_Codes",
>>>RefersToR1C1:= _
>>>        "='Project Codes'!R5C1+'Project Codes'!

> R5C1:R13C4"

>>>End Sub

>>>How do I create a dynamic range name using VisualBasic
>>>code, such that I can select the entire list and assign
>>>the new range to the name "Project_Codes"?

>>>Can anyone help?

>>>Dirk Schmid
>>>North/South Consultants Inc.
>>>Winnipeg, Canada

>>.



Fri, 09 Dec 2005 19:24:49 GMT  
 Dynamic Range Names using VBA
Alan,
Just wondering.  Is my post, the first to respond, visible?  It showed:

Range("Project_Codes")(1).CurrentRegion.Name = "Project_Codes"

Regards,
Tom Ogilvy


Quote:

> > Thank you for your help. The solution is simpler than I
> > thought! :-)

> It still is: Range("A5").CurrentRegion.Name = "Project_Codes"

> Alan Beban

> >>-----Original Message-----
> >>Dirk try this

> >>This code is totally dynamic for any lenght of data

> > files.

> >>Sub UpdateRange_Project_Codes()
> >>   Range("A5").Select
> >>   Selection.CurrentRegion.Select
> >>   Selection.Name = "Project_Codes"
> >>End sub

> >>Regards,

> >>Fernando Ortiz



> >>>Greetings.

> >>>I'm really stumped. I have an Excel worksheet set up

> > with

> >>>a 3-column list of project codes, project descriptions,
> >>>client names. I've defined a range name for the entire
> >>>list (i.e., Project_Codes). This list will expand and
> >>>contract over time. I could manually re-define the

> > range

> >>>name if the list expands or contracts, but a Visual

> > Basic

> >>>macro could automate the procedure and integrate with
> >>>other macros I've written.

> >>>I recorded the following VB macro in Excel, which, of
> >>>course, is impractical (see below):

> >>>Sub UpdateRange_Project_Codes()

> >>>    Range("A5").Select
> >>>    Selection.CurrentRegion.Select
> >>>    ActiveWorkbook.Names.Add Name:="Project_Codes",
> >>>RefersToR1C1:= _
> >>>        "='Project Codes'!R5C1+'Project Codes'!

> > R5C1:R13C4"

> >>>End Sub

> >>>How do I create a dynamic range name using VisualBasic
> >>>code, such that I can select the entire list and assign
> >>>the new range to the name "Project_Codes"?

> >>>Can anyone help?

> >>>Dirk Schmid
> >>>North/South Consultants Inc.
> >>>Winnipeg, Canada

> >>.



Fri, 09 Dec 2005 22:19:47 GMT  
 Dynamic Range Names using VBA
Not in the thread "Thank you", which is where I saw the post by Fernando
Ortiz, to which I responded. On searching, I found yours in a thread
called "Dynamic Range Names using VBA", a thread I hadn't previously
looked at.

:-)
Alan

Quote:

> Alan,
> Just wondering.  Is my post, the first to respond, visible?  It showed:

> Range("Project_Codes")(1).CurrentRegion.Name = "Project_Codes"

> Regards,
> Tom Ogilvy




>>>Thank you for your help. The solution is simpler than I
>>>thought! :-)

>>It still is: Range("A5").CurrentRegion.Name = "Project_Codes"

>>Alan Beban

>>>>-----Original Message-----
>>>>Dirk try this

>>>>This code is totally dynamic for any lenght of data

>>>files.

>>>>Sub UpdateRange_Project_Codes()
>>>>  Range("A5").Select
>>>>  Selection.CurrentRegion.Select
>>>>  Selection.Name = "Project_Codes"
>>>>End sub

>>>>Regards,

>>>>Fernando Ortiz



>>>>>Greetings.

>>>>>I'm really stumped. I have an Excel worksheet set up

>>>with

>>>>>a 3-column list of project codes, project descriptions,
>>>>>client names. I've defined a range name for the entire
>>>>>list (i.e., Project_Codes). This list will expand and
>>>>>contract over time. I could manually re-define the

>>>range

>>>>>name if the list expands or contracts, but a Visual

>>>Basic

>>>>>macro could automate the procedure and integrate with
>>>>>other macros I've written.

>>>>>I recorded the following VB macro in Excel, which, of
>>>>>course, is impractical (see below):

>>>>>Sub UpdateRange_Project_Codes()

>>>>>   Range("A5").Select
>>>>>   Selection.CurrentRegion.Select
>>>>>   ActiveWorkbook.Names.Add Name:="Project_Codes",
>>>>>RefersToR1C1:= _
>>>>>       "='Project Codes'!R5C1+'Project Codes'!

>>>R5C1:R13C4"

>>>>>End Sub

>>>>>How do I create a dynamic range name using VisualBasic
>>>>>code, such that I can select the entire list and assign
>>>>>the new range to the name "Project_Codes"?

>>>>>Can anyone help?

>>>>>Dirk Schmid
>>>>>North/South Consultants Inc.
>>>>>Winnipeg, Canada

>>>>.



Sat, 10 Dec 2005 00:37:59 GMT  
 Dynamic Range Names using VBA
Alan,
Thanks.
Just wondering - I thought it hadn't shown up or something.  These all show
in the same thread for me.  Since it was more concise than Fernando's  3
step process, but ignored and then you suggested the same improvement I was
just wondering.  I guess Dirk prefers recorder code.  Go figure <g>.

Thanks for checking.

Regards,
Tom Ogilvy


Quote:
> Not in the thread "Thank you", which is where I saw the post by Fernando
> Ortiz, to which I responded. On searching, I found yours in a thread
> called "Dynamic Range Names using VBA", a thread I hadn't previously
> looked at.

> :-)
> Alan


> > Alan,
> > Just wondering.  Is my post, the first to respond, visible?  It showed:

> > Range("Project_Codes")(1).CurrentRegion.Name = "Project_Codes"

> > Regards,
> > Tom Ogilvy




> >>>Thank you for your help. The solution is simpler than I
> >>>thought! :-)

> >>It still is: Range("A5").CurrentRegion.Name = "Project_Codes"

> >>Alan Beban

> >>>>-----Original Message-----
> >>>>Dirk try this

> >>>>This code is totally dynamic for any lenght of data

> >>>files.

> >>>>Sub UpdateRange_Project_Codes()
> >>>>  Range("A5").Select
> >>>>  Selection.CurrentRegion.Select
> >>>>  Selection.Name = "Project_Codes"
> >>>>End sub

> >>>>Regards,

> >>>>Fernando Ortiz



> >>>>>Greetings.

> >>>>>I'm really stumped. I have an Excel worksheet set up

> >>>with

> >>>>>a 3-column list of project codes, project descriptions,
> >>>>>client names. I've defined a range name for the entire
> >>>>>list (i.e., Project_Codes). This list will expand and
> >>>>>contract over time. I could manually re-define the

> >>>range

> >>>>>name if the list expands or contracts, but a Visual

> >>>Basic

> >>>>>macro could automate the procedure and integrate with
> >>>>>other macros I've written.

> >>>>>I recorded the following VB macro in Excel, which, of
> >>>>>course, is impractical (see below):

> >>>>>Sub UpdateRange_Project_Codes()

> >>>>>   Range("A5").Select
> >>>>>   Selection.CurrentRegion.Select
> >>>>>   ActiveWorkbook.Names.Add Name:="Project_Codes",
> >>>>>RefersToR1C1:= _
> >>>>>       "='Project Codes'!R5C1+'Project Codes'!

> >>>R5C1:R13C4"

> >>>>>End Sub

> >>>>>How do I create a dynamic range name using VisualBasic
> >>>>>code, such that I can select the entire list and assign
> >>>>>the new range to the name "Project_Codes"?

> >>>>>Can anyone help?

> >>>>>Dirk Schmid
> >>>>>North/South Consultants Inc.
> >>>>>Winnipeg, Canada

> >>>>.



Sat, 10 Dec 2005 04:52:41 GMT  
 
 [ 9 post ] 

 Relevant Pages 

1. Finding the first used cell in a dynamic named range using code

2. Dynamic Named Ranges in VBA

3. Dynamic Range name coding with VBA possible?

4. Excel VBA Dynamic Naming Ranges

5. using Dynamic named ranges

6. Using Dynamic Named Ranges

7. Dynamic Lookup Using Named Label Ranges

8. Using Dynamic Named Ranges

9. Dynamic Range Names used in Macros

10. Using Dynamic Named Ranges


 
Powered by phpBB® Forum Software © phpBB Group