Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > VBA / Office Integration > Word, PowerPoint, Outlook, and Other Office Products > Naming an Excel range


Reply
 
Thread Tools Display Modes
  #1  
Old 10-03-2002, 03:11 PM
TrentG TrentG is offline
Centurion
 
Join Date: May 2002
Posts: 181
Default Naming an Excel range


Hi

I have an Excel macro and that selects certain rows (based on a text string) and then copies that row to another worksheet. This is working fine.

However, after all the necessary rows have been copied and pasted to the other sheet, I want to assign a range name to all of the copied rows. I am using the CurrentRegion.Select syntax to select all of the data, but I do not know how in VBA to give this range a name.

Any help would be great.

Thanks
Reply With Quote
  #2  
Old 10-03-2002, 03:48 PM
IanPatton IanPatton is offline
Junior Contributor
 
Join Date: Jul 2001
Location: UK
Posts: 216
Default

This is how to give a range a name in VBA

Code:
Names.Add Name:="Your Range Name", RefersTo:="=$A$1:$A$10"
__________________
Regards
Ian
Reply With Quote
  #3  
Old 10-04-2002, 06:22 AM
TrentG TrentG is offline
Centurion
 
Join Date: May 2002
Posts: 181
Default

Thank you for the reply.
Unfortunately, I cannot hard code any cell references because the size of the range will change each time I run my macro.
Is there a way to put something in the RefersTo property so that it will change dynamically?

Thanks
Reply With Quote
  #4  
Old 10-04-2002, 06:30 AM
vb6guru2
Guest
 
Posts: n/a
Default

CurrentRegion returns a range object, so:

Code:
Names.Add Name:="Your Range Name", RefersTo: = active.currentrange.range

Hope it help[s!
Reply With Quote
  #5  
Old 10-04-2002, 06:45 AM
TrentG TrentG is offline
Centurion
 
Join Date: May 2002
Posts: 181
Default

I pasted in the code from the previous post (vb6guru2), but I am getting an "Object required" error.
That section of my code looks as follows:
Code:
Range("A1").CurrentRegion.Select
Names.Add Name:="Import", RefersTo:=active.currentrange.Range
Any idea what I am doing wrong?

Thanks
Reply With Quote
  #6  
Old 10-04-2002, 07:15 AM
vb6guru2
Guest
 
Posts: n/a
Default

sorry, my typo... it should be:

Code:
Range("A1").CurrentRegion.Select Names.Add Name:="Import", RefersTo:=activecell.currentrange.Range
Reply With Quote
  #7  
Old 10-04-2002, 07:45 AM
TrentG TrentG is offline
Centurion
 
Join Date: May 2002
Posts: 181
Default

Thanks, that works!!!!!!
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
 
 
-->