VB Code for Naming Ranges

Can somehere tell me how to name a range in excel using VB code.  Here'
how I get the range:

Range("L1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False


As u can see the range changes, but I would like to create the sam
name for whatever the range is?

Thanks.

--
Message posted from http://www.ExcelForum.com

0
5/12/2004 4:08:27 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
559 Views

Similar Articles

[PageSpeed] 53

the code you can use is 

ActiveWorkbook.Names.Add Name:="YOURLabel"

so say your rang was L1:L33 you could have

Range("L1:L33").Select
ActiveWorkbook.Names.Add Name:="yourname"
RefersToR1C1:="=Sheet1!R1C12:R33C12"

After this when refering to that range it can be

Range("yourname")

Altnerativly you can do the following

dim your name as range
set yourname = range("L1:L33")

'refer  to your range

yourname.Selec

--
Message posted from http://www.ExcelForum.com

0
5/12/2004 4:25:29 PM
Here are two general Ideas.  CurrentRegion works if the area is surrounded
by blank cells.  On the other hand, "End" is a problem if you have blank
cells along the edge.  Depends on how your data is set up.

Sub Demo()
    With [L1].CurrentRegion
        .Copy
        .PasteSpecial Paste:=xlPasteValues
    End With

    'Or
    With Range([L1], [L1].End(xlDown).End(xlToRight))
        .Copy
        .PasteSpecial Paste:=xlPasteValues
    End With
End Sub

-- 
Dana DeLouis
Using Windows XP  &  Office XP
= = = = = = = = = = = = = = = = =


"krazylain >" <<krazylain.165hw1@excelforum-nospam.com> wrote in message
news:krazylain.165hw1@excelforum-nospam.com...
> Can somehere tell me how to name a range in excel using VB code.  Here's
> how I get the range:
>
> Range("L1").Select
> Range(Selection, Selection.End(xlDown)).Select
> Range(Selection, Selection.End(xlToRight)).Select
> Selection.Copy
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Application.CutCopyMode = False
>
>
> As u can see the range changes, but I would like to create the same
> name for whatever the range is?


0
delouis (422)
5/13/2004 12:20:56 AM
Reply:

Similar Artilces:

Can't send e-mails
I can receive e-mails but not send them. Dialogue box states: "Please enter your user name and password for the following server. server: smtp.frontier.com user name: ___________ password: _____________" (I've not filled in user name & password here.) Is this a settings, or hardware (server) problem? Do I fix by changing live mail settings, or call my DSL provider regarding their modem/router? Has this e-mail account ever worked? You can't have an e-mail account without a username and password. Tools | Accounts | Mail | Properties | Servers. Is t...

Mutual fund codes
I have a mutual fund that I cannot get the prices for. The code is CA:AIM3593 and Money does not list that fund. I'm upgrading from Quicken 2002, which could download prices for this fund and I'm deciding between the latest versions of Money and Quicken. There are 7 other funds that I cannot get prices for because Money does not list the fund. The codes are all available at https://www.cannex.com/canada/english/fund/fp030ae.html and all work with Quicken. -- If you can keep your head about you, while all those around you are losing theirs, you just don't understand the...

IF Formulas with Ranges
Hi! I'm trying to create an IF formula where the workbook will recognize if there is data added to a column D range (Sessions) and then apply a formula to the last cell of column F range (Aimline). Hence, if Sessions (column D) which now has the numbers 1-27 in it gets 28 entered below, then the cell in the same row but column F will take the number 28 and apply the formula above it =$F$8+($A$12*D35). Can anyone help? cabybake =IF(D27="","",$F$8+($A$12*D27) made an assumption that D35 should relate to D28 -- HTH Bob Phillips (remove nothere from email address...

VB distmon?
Hi I have recently installed Office 2003. A couple of wired things happen in Excel: > Everytime I close the program I get a MS VB error that reads "Compile error in hidden module: Distmon" Every time open the program I get the same error, but the macro it is calling is "autoexecnew" > Since my new installation, I type in an simple function like TRIM and it tells me cannot find project library. any ideas? Thnaks! Alan Alan http://support.microsoft.com/default.aspx?scid=kb;EN-US;q307410 OFF: "Compile Error in Hidden Module" Error Message When You Sta...

Date Range
Using SQL 2005 Is there a way to query a date range with only one date provided? The date field is a timestamp with mm/dd/yyyy mm:ss. User provides date from a dropdown list on a form - example 12/01/2009. We want to provide all records that fall in the month of Dec 2009. SELECT [DATE] FROM tbl WHERE [DATE] >= '12/01/2009' ??? How to get just the dates for that month? Your help is greatly appreciated -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/201002/1 You can calculate range from first of the mont...

Colorising cells from code
I am using the code below which runs whenever a cell is edited -I think. I would like to change this code so that it can be 'run' against a range of cells rather than as Worksheet_Change code. What do I need to do to do that ? Thanks Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Range("A1").Value = "" Then If Not Intersect(Target, Range("B4:J34, B35:B39")) Is Nothing Then Select Case Target Case Sheet3.Range("A4") icolor = 34 Case Sheet3.Range("A5") icolo...

IP address for each DNS name?
Can I have my exchange server operate on two IP address each with different names as if they were two servers? Please don't ask why I want to, but can this be done? Server1.example.com: mail.example.com, 192.168.1.1 (all mail for example.com sent/received exclusively for this IP) Virtual SMTP Server: mail.domain.com 192.168.1.2 (all mail for domain.com sent/received exclusively for this IP) "you know who maybe" <nguser2u@spamnotAOL.com> wrote: >Can I have my exchange server operate on two IP address each with different >names as if they were two servers? ...

Why Range name not showing in list
I have been studying creating range names in VBA. I have two procedures that work (shown below.) When the first example is run, the range name does not display in the toolbars Name Box but the second one does. I don't understand why and if I create the name using the first method, how can I get it to show up in the Name box list? 1st Example ActiveWorkbook.Names.Add Name:="ListTbl", _ RefersTo:="=Offset(Sheet2!$A:$A,0,0,COUNTA(Sheet!$A:$A),4)" 2nd Example ‘ Sheets("ListTbl").Activate ' ' With ActiveWorkbook...

Duplicate detection (looking for code)
I would like to add code to detect for duplicates or records with similar information. Looking to do this check on (firstname and lastname) and on address (street1, street2, city, state, zip) But I want something a little more advanced than just checking for exact matches. Wondering if anyone has some code they would care to share that might make my job of writing it a little easier? Example: Bob Smith and Bobby Smith would be detected as duplicates Rob Jones and Robert Jones would be detected as duplicates 123 main street pittsburgh, pa 15126 123 main st pittsburgh pa 15...

Bar code scanners
Hello All! I have a warehouse facility and several stores and am trying to find a way to use a barcode scanner with internal memory to scan large orders (200 pieces or greater) that i can then hook to my pc with RMS and "dump" the information into either a transfer out or an invoice. has anyone done this? if not does anyone have any recommendations? is there a person i could contact that may be able to write a short program or executable to make this happen? I would appreciate any and all information anyone may be able to provide. Thanks, Jim Hi Jim, I don't kno...

EAN 14, Dun 14
Hi, I have a printer TLP 2844 (Zebra) and I need to print bar codes using EAN 14 or Dun 14. Anyone have any idea how I can do that? I want just a simple sample in how do that. I posted this question here in excel forum because there isn't any group related as bar codes and because I want to work with that using just Excel. Thanks in advance, Magno magno_jr@terra.com.br You will need to buy a software designed for barcode like Lableview. EAN14 is not a common barcode so make sure it will do that. If you call Zebra, I think they sell something, if not they can recommand on...

Code does not work on merged cells
The following code works fine on single cells but I also have Dat Validation which for cosmetic purposes merges to the next cell e.g Cell B3 merges into cell C3 - why, if I use either B3 or B3:C3 in th code below does it not work? i.e. I should get an error message if try to delete data from one of the cells. Thanks _Code_ Application.EnableEvents = False If (Len(Range("b10")) = 0) Or (Len(Range("c10")) = 0) O (Len(Range("f10")) = 0) Then With Target If .Value = "" Then Application.EnableEvents = False .Value = "Invalid" MsgBox "You ...

Swing Chart with dynamic Range
I would like to create a swing chart (stock chart) with a dynamic range that grows as more data is added. Any ideas? Thank You Joe Hi, For information on the dynamic ranges have a read of Jon Peltier's page http://peltiertech.com/Excel/Charts/Dynamics.html Depending on which stock chart you want you will need 3 to 5 named ranges. Cheers Andy Joseph Sayah wrote: > I would like to create a swing chart (stock chart) with a dynamic range that > grows as more data is added. > > Any ideas? > > Thank You > Joe -- Andy Pope, Microsoft MVP - Excel http://www.andyp...

Excel VB
I have two cells in a workbook that are dates. The cell Start_Date has a valid date in it. The second cell Test_Date is either blank or has a valid date in it. The IsDate test on both cells works fine, however, on a blank cell the Month function extracts "12". Anyone know why? I had to put the month function within a test for IsDate to make it work. Seems like Month function on a blank cell should return something other than a 12. Dave Test code: If IsDate(Range("Start_Date")) Then 'Cell has 11/01/09 in it. MsgBox ("start date a d...

Cannot Add any window to VB
Hello: I'm using a VM with GP 10.0 SP3 installed. In order to have the same as our production databases i copy the GP folder, entirely, which i think it was a mistake now I'm getting a "This cannot be added to Visual Basic", whenever i try to add a window, any window, to VB. Also it goes without saying that there's a lot of load errors starting GP. For the record the copy/paste thing worked, sort of, and before you start thinking that i was insane to have done this, there's a reason behind it, There's been a lot of tweaks to our production server, by our p...

vb from VC++
Hi, is it possible, and if so, to call a Vb macro from VC++. Or manipulate spreadsheets (ie draw graphs from the data within the workbooks) usinbg MFC functions cheers Anthony Presumably you are referring to MS Excel spreadsheets and workbooks. The easiest method would be to import the appropriate type library (.tlb) provided with Excel, having the class wizard generate the classes, which you then use to access the Excel Automation model (that is the same model that Excel VBA uses). Some investigation may be needed to determine which tlb to use, and to make sure you have a basic understand...

Subscript out of range
i keep getting an subscript out of range error here [lrowcountref is active rows, start in e2 where the data starts, hence 2 to rowcount] ReDim arrEmailAdd(2 To lRowCountRef, 2 To lRowCountRef) Range("E2").Select For z = 2 To lRowCountRef ********here is the error********* For x = 2 To lRowCountRef arrEmailAdd(z) = ActiveCell.Value arrEmailAdd(x) = ActiveCell.Offset(0, -3).Value ActiveCell.Offset(1, 0).Select Next Next Your problem is that you have defined a 2D array, but you are trying to load it as if it were a single dimension. I am struggling to see what you...

Auto assigning department Codes to a Project
We have many projects set up - within each project we assign multiple resources. The resources are often from different departments. Today, we set up a project for each department as we cannot seem to make PA "dynamically" assign our department value based upon the person assigned to the project. Is it possible to have a project dynamicaly assign a department code (a GL segment) based upon the person assigned to the project? -- Dave Not without customization using VBA or Dexterity. -- Charles Allen, MVP "David Schmidtknecht" wrote: > We have many project...

VB in Excel
In Excel I go into Visual Basic Editor, then Visual Basic Help, but when I type 'Passwords' for example it will bring up a list of related items, but some of them you can't select. Does this mean the installation CD is corrupt and hasn't installed all the components? I have reinstalled and repaired the installtion but to no avail. Any ideas anyone? Help! Thanks ...

Domain name required?
Hello - I'm running Win03 w/ SP1 & Exchange 03 w/ SP2. My question is when users connect via OWA they can only do so if they specify domain\username. We have another company that when users log in to OWA all they need it the username. Where is this defined? How can I change this? Thanks Article goes over instructions. How to configure Exchange Server 2003 so that users can log on to OWA without entering a domain name http://support.microsoft.com/kb/903942 To enable Outlook Web Access to accept user logons without the domain name, follow these steps on all Microsoft Excha...

Difference between range and array
Dear All Could someone please explain to me what is the difference between "range" and "array"? Thanks in advance, Paul A range is a set of contiguous cells within a spreadsheet, that is a number of cells within a column or a row, or an a group of n rows and m columns. A range is rectangular in shape. An array is a series of objects, such as an array of integers, an array of strings, or even an array of range objects <G>. In a worksheet, an array formula works upon a range of cells, and so is implicitly linked to that range. In VBA, an array can be loaded with ...

automatic name filling
Hi: With Outlook Express, one only needs to type one letter of the recipient name or alias, and the complete name is shown. Can this be done with Outlook 2002? Thanks, aap Yes. Tools > Options > Email > Options > Advanced Email options > "Suggest names..." You won't see it function until you have sent enough messages to populate your autocompletion cache. -- Russ Valentine [MVP-Outlook] <anonymous@discussions.microsoft.com> wrote in message news:016d01c3a62b$b1b660f0$a101280a@phx.gbl... > Hi: > > With Outlook Express, one only needs to type...

I cannot select a range
I wrote a macro, inter alia containing the following code: Const constName = "JCMail.xls" Const constPre = "JC" Private Sub CommandButton1_Click() ' Application.ScreenUpdating = False ' Gets and Inserts job card number Range("AB3").Select ' CommandButton1.Deactivate varRef = ActiveCell.Value copier End Sub Private Sub copier() ' Copies and pastes date Windows(constName).Activate Range("G3").Select When the macro reaches the last line of code (Range("G3").Select), I get the following error messa...

Cntrl find
I have a large excel worksheet - zip codes, cities, states, area codes ... I need to be able to sort as well as find. Problem comes in with states that have zips starting with zero. I can get them to appear correctly as custom or special, but not able to do cntrl-find and bring up ... I can't believe I can't figure this out ... Format the Zip code column as TEXT and enter the leading zero normally as any other character.........then FIND will "find" it........... Vaya con Dios, Chuck, CABGx3 "jjjJackieCalifornia" <jjjJackieCalifornia@discussions.microsoft...

Printing Customer name on Receipts
Hi, I posted a question recently asking how to print customer's name on our receipts especially for credit card transactions where the name is automatically printed on Customer's copy but not our own. Some answers I received lead me to feel that I need to edit some XML files - but the actual answer is much simpler and I wanted to share it here. All you need to do is go to Manager/Database/Registers/Receipt Formats. Choose the "40 Column Receipt Format" and choose to edit the "Sales" receipt. Then in one of the Footer Lines (near bottom) you can just ente...