Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Vba To Change Named Ranges

I was given the

What I am looking to do is have a range of cells that I have named to be used in a data validation for a drop down box. I have named the cells and would like to have the user name the cells by typing in the name into one cell.

For example

Cells F10:F14 are named "orange" right now (typed in cell F9)
I would like the user to be able to change the name of the cells to whatever they want by changing the cell in F9 - i.e. "banana" and the range is automatically named banana so the data validation can search for Banana instead.


Dim oldName

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Rng As Range
Set Rng = Range("F9")
If Not Intersect(Target, Rng) Is Nothing Then
Range(oldName).Name = Range("F9")

This Code works great for 1 range - but I have 30 - I can't seem to figure out how to copy it etc for the other 30 ranges.

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Named Ranges On Multiple Sheets With The Same Named Range & I Cant Figure Out How To Do This
I need to create a named range on multiple sheets with the same named range & i cant figure out how to do this. EG :- I want to create a named range called "_SubUnitRows" on sheet1 starting from "A1:A50" & other named range again called "_SubUnitRows" on Sheet2 starting from "A1:A25" ...

View Replies!   View Related
Named Ranges From VBA
I have some named ranges that refer to 5 pieces of data organized into a row. For example, the name MyNamedRange might refer to $C$5:$C$10.

I am trying to loop through each column and get the values in MyNamedRange, then change corresponding values in a different named range. However, when I try to use Offset to access the subsequent columns of MyNamedRange, it doesn't work. It only gets the value of the first column right, the rest return <EMPTY>.

Sample .......

View Replies!   View Related
Sum Of X Named Ranges
the code to add two independent named ranges cell by cell in vba. Both of the named ranges have the same structure and the sum would be posted to a third area of the same structure cell wise.

View Replies!   View Related
Using Named Ranges.
How do I go about using named ranges instead of cell names(ie A4:A9)

Private Sub UserForm_Activate()
Dim lngRow As Long
Dim intIndex As Integer
UserForm1.ComboBox1.Clear 'Clear combobox
lngRow = 2 Do While Sheet1.Range("a" & lngRow).Value <> ""
For intIndex = 0 To UserForm1.ComboBox1.ListCount - 1
If UserForm1.ComboBox1.List(intIndex) = Sheet1.Range("a" & lngRow).Value
Goto NextRow
End If
Next intIndex
UserForm1.ComboBox1.AddItem Sheet1.Range("a" & lngRow).Value
lngRow = lngRow + 1
End Sub

I wanted to change "a" to the defined range "search"?

View Replies!   View Related
Delete Named Ranges
I have a number of crazy defined names in my spreadsheet (1,746 to be exact). Not sure where they all came from, but I need them gone gone gone. Some of them start with squares at the front of the name and most of them refer to =#ref! or similar. I've seen a number of vba solutions to this, but none of them seem to work for me - I get "400" errors or run time errors. For reference, I'm hitting Alt F11, pasting the whole sub in, saving, then running.

View Replies!   View Related
Referring To Named Ranges
I'm creating a button so as to input a chart where the user inputs the name of the range that they want the chart to hold. The named ranges are dynamic thus I want the graph to also be dynamic but when added the source is simply the range of the name rather than the name itself.

Is there anyway I can enter in a VB variable into quotations marks so that it simply writes that.

View Replies!   View Related
Vlookup With Named Ranges
I am having problems getting the code below to follow my intentions. I have 2 files. I have a temporary file that holds all the data that I need. I have a destination file that needs to have data copied into it. In the temp file, I have data for several dates and product types. What I intend to do is to do a vlookup in VBA to look for the date and the product type in the temp file and copy the appropriate data to the destination file. I have numerous named ranges both in my temp file and my destination file. For the code below, I wanted to make a loop to find the date in the temp file that is listed in the destination file. Once this is done, I wanted to find the product type in the temp file that is listed in the destination file. If both conditions exist, then copy a certain range from the temp file to the destination file. My intention is evident in the code, but I don’t think that I am putting in the correct “code format”.

View Replies!   View Related
Graph Using Named Ranges
Is it possible to create a chart based on value from a drop down?

The drop down has two values which are both named ranges.

There for I want to be able to choose from the drop down whether to display data per team or per agent.

View Replies!   View Related
Named Ranges In Workbook
I have got a named range, which gives me a list of all the students. I would like to put that data returned in named range in excell workbook.

For example:
named range called "Students" is getting populated from A1:A10 with values:

I would like to put this data in other worksheet.

and instead of putting all the students, it just puts only

I entered in named range name ("students") in all cells I wanted to put data in.

View Replies!   View Related
Clear Named Ranges
There are over 100 named ranges in my excel 2007 workbook. Is there an easy way to clear all of these out?

View Replies!   View Related
Referencing With Named Ranges
Ok I have been trying to figure this out for way to long. Say I have a list of values in A1:A4. I have named this range MyRange. If I wanted to refer to this range somewhere else in the workbook and enter =MyRange it will only work provided the "=MyRange" is in the same rows as the original data.

For example if I enter MyRange in cells C1 through C4 it would mirror the data in column A. But if I enter MyRange in C5:C8 I would get a #Value error. Does anyone have any ideas to make this more of an absolute reference.

Basically anywhere I enter MyRange into a cell I want to see A1. If i drag that formula down I want to see A2, A3, A4, etc....

View Replies!   View Related
Duplicating Named Ranges
I'm creating a KPI spreadsheet which utilizes named ranges to allow for Dynamic charting. I've created the first data input sheet for one of the 10 areas being KPIed. The sheet has 60 named ranges in it.

The goal is to duplicate the existing sheet (Area 1A) 10 times and adjust the named ranges and formulas within the named ranges according to the sheet names.

Is there a way to accomplish this without having to manually recreate or edit every named range for each new sheet?

View Replies!   View Related
Union Named Ranges
(Using Office 2003 on XP Pro) I have two named ranges that I want to union into one big named range so that I can use the big named range in a validation table. Unfortunately the big range does not appear on the list of named range so I switched to VBA to try and lick this but really I was hoping a non-VBA solution exists. Exploring possible VBA solutions, here is what I have so far:

View Replies!   View Related
Named Ranges In A Chart - .
I have a spreadsheet with 300+ questions in columns (covering 2 worksheets - rows 1 to 5), and the answers to the questions are placed in the rows beneath - column A & B lists who the answers came from.

The idea here is that on a seperate sheet a user can enter a question number (e.g: 1.2OP007) and a graph will update to show the different values given for each person who answered that question.

So, in this seperate sheet I have the question in cell $A$1.
In cell $B$1 I have this formula:.........................

View Replies!   View Related
Mirroring Named Ranges
The worksheet contains 20 different datasets which are divided into separate 30 x 10 grids, all of which help comprise a larger 300 x 20 grid. I have defined each smaller grid/dataset as its own named range. Originally the entire dataset started in A1, but I am shifting it dataset down by 40 lines to create space to work at the top of my workbook.

I am wanting to create a data validation list in A1, then have cells A3:J32 populate with the named range I select in A1. However I also want this populated dataset at the top of the sheet to be a mirror -- not a copy.

Therefore once I have the data in my range available A3:J32, anything I manipulate would be mirrored in the original range in the lower portion of the workbook.

View Replies!   View Related
Import Named Ranges
I am importing an external Sheet into my Excel workbook but it only imports the data not the named ranges. I need the named ranges to be imported and overwrite any existing range of same name.


In existing workbook Sheet I have a named range, ABC which is A1:E10 - in the Sheet I am importing the ABC range is A1:E12. So when I have done import I want named range ABC in my workbook to become A1:E12 (as per external Sheet).

This is the code that import data (but not named ranges)

View Replies!   View Related
Merge 2 Named Ranges
can I merge 2 named ranges with a build-in function and give this merged range a 3rd name ?

View Replies!   View Related
Copy Named Ranges
I have a workbook with about 25 named ranges that I would like to use in a a different workbook. Is there some way to copy them, or a way to transfer them through code?

View Replies!   View Related
Selecting Two Named Ranges
I have two named ranges, LV1 and LV2 and wish to select them for printing. If the ranges were fixed I would use - Range("A2:H4,A8:H10").Select - how do I do the equivalent using the named ranges in vba?

View Replies!   View Related
Create A Set Of Named Ranges
I'm trying to make my worksheet generate a bunch of named ranges whenever it is activated. However, I'm getting a "Not a valid name" error on the line in red. The value of r is a string, so I really don't see what the issue is. Another time, I got a object defined error, but I think I've declared everyhing too. That line is very similar to the line above, and tends to work once (the first time around the loop). After that, I get the errors I mentioned.

View Replies!   View Related
Dynamic Named Ranges ...
Dynamic named ranges - lots of them! ...

View Replies!   View Related
Replace Two Named Ranges
What is the correct syntax if I want to replace two named ranges below to Rng and Rng2 in my code?

View Replies!   View Related
VLookup, Named Ranges And IF
=VLOOKUP('ITEM ADD-CHG Form'!Z19,Brand_V,2,FALSE) Another wrench!. If the value in cell Z19 is not found in the named range "Brand_V", the formula returns #N/A. I need to replace #N/A with the verbiage DEF while leaving the other results intact.

View Replies!   View Related
Macro For Two Named Ranges
Macro for two named ranges. I have this code, which works fine...

View Replies!   View Related
Print Named Ranges ..
I want to create a very simple macro to print some ranges that I have defined in my workbook. The ranges are as follows:

Note: Each of these ranges is in a different worksheet named Cons_Summ, Esky_Summ, Indy_Summ, and Gfld_Summ respectively.

To be printed in portrait view:
- Cons_Summ_IS
- Esky_Summ_IS
- Indy_Summ_IS
- Gfld_Summ_IS

To be printed in landscape view:
- Cons_Summ_OH
- Esky_Summ_OH
- Indy_Summ_OH
- Gfld_Summ_OH

I just want to create a macro so I can run it and it will print all of the ranges listed above with the proper orientation. Nothing special, each range must fit on 1 page x 1 page.

View Replies!   View Related
Resize Named Ranges
I have to refer to a named range in a macro, but in the beginning I don't know what size it will be. So, in the beginning myRange will always consist of a single cell, which later on will be the 1st cell of the correctly sized range. While the macro runs, the correct size of the range is calculated, and at that point the range has to be resized, say to 'n' rows by 'm' columns, with the original cell as 1st cell.

View Replies!   View Related
Expanding Named Ranges And Access
Why do my named ranges expand each time I import data from Access? I then have to go my named ranges and reset them. The reason I am using named ranges is because of the indirect functions I am using.

View Replies!   View Related
Loop Defining Named Ranges
I'm trying to define some ranges with a loop but being a newbie at VBA I'm finding it difficult to solve the error in the code.
First, as the data area is uncertain (variation in the number of rows and columns) i look to the end of the entire data area. Then i want to define intervals of 250 rows with a 30 row difference between each of them. Here is my

View Replies!   View Related
Non-Volatile Dynamic Named Ranges
I am looking for a non volatile alternative to using OFFSET in my named ranges as my models are generally quite bulky and it is beginning to make me quite angry having to build these models in calc manual.

Can anybody provide a non-volatile alternative to this?


With Height being:


View Replies!   View Related
Compare Named Ranges With Formulas
i have an excel sheet with two columns, and depending on the status of the equipment, i need to compare the two columns and if the data is the same, fine, otherwise i need to display a msg box with an error. the columns have formulas in them, and i assigned a name to the cells i want to check, i keep getting errors with the following code, when the colums are the same, it is still displaying the msg box with the error. what am i doing wrong?

For Each Row In Range("Dev_Found") 'Loop through each row in Column C
If Row.Value ActiveWorkbook.Names.Item("Dev_Left").Value Then
blah = MsgBox("Your % Dev for after does not match % Dev before, please correct on form!", vbOKOnly, "Error")
Exit For
End If
Next Row

View Replies!   View Related
Macro To Remove Named Ranges With #REF
VBA code to remove all named ranges in a workbook that contain "#REF" in the reference. I have some legacy workbooks and some have hundreds of invalid named ranges, it's painstakingly slow to delete them manually, a macro to wipe out the ones with #REF in them


Names in Workbook:

Refers to:

This one has the #REF in the reference, I'd like ones like this go be wiped out.

View Replies!   View Related
Array Constant Named Ranges
Is it possible to make an named array constant from named constants to use in a lookup?


I have defined the following constants in Names:

Num1 = "1234567890"
Num2 = "0987654321"

And would like to make the following array constant in Names:

Arr = {Text1,Num1;Text2,Num2}

However, it does not work.

I am using Excel 2007 by the way and the above values are made up.

View Replies!   View Related
Named Ranges, Application Vs. Worksheet
Can named ranges defined using the Application.names.add method be accessed using the worksheet(x).names(name) property?

I would like to access only the named ranges on one worksheet, but, it seems to me that the names collection of my worksheet contains no names.

If this is a result of my declaring the named ranges using the application.names.add method, is there a worksheet property that I could use to access only the named ranges on that worksheet?

View Replies!   View Related
INDIRECT And Dynamic Named Ranges
I have a dynamic named rage "AREA" which can contains values like "Area1, Area2, Area3, ..."

For each item in list AREA I have another dynamic named range, named exactly as the item, so for example I have a dynamic named range "AREA1" with values "SubArea1.1, SubArea1.2, ..." and also a dynamic named range "AREA2" with values "SubArea2.1, SubArea2.2, ..." and so on, I think it's pretty clear and straight forward (I am modelling the fact to have several areas and each of them has several sub areas).

Now, I have cell A1 being validated against the list AREA, that is, the source for A1 is "=AREA", so that I can choose one of the values in the named range AREA; I also have cell A2 being validated against the list that I choose in cell A1 using the INDIRECT function, that is, the source for A2 is "=INDIRECT(A1)".

I have already realised that this approach for cell A2 does not work, as it seems that INDIRECT does not work for dynamica named cells.

An option would be using the following for the source of A2: ....

View Replies!   View Related
Max Of The Values In This Column In Two Different Named Ranges
I have two named ranges, let's call them A and B, each referring to a different single row in a worksheet.

I have another row in which I would like to compute the maximum of the values found in A and B in that column.

=MAX(A,B) gives the maximum value of all the values found over the entire ranges A and B, rather than just those in the current column.

What formula should I use to find the maximum of the values found in range A and range B in the current column?

View Replies!   View Related
Dynamic Validation Without Using Named Ranges
I have a question regarding dynamic validation. I have attached a sample spreadsheet to clarify if my explanation doesn't. I have a list of codes that map to a list of other codes that are not one to one mapping. I would like for the other users of this code to populate what the correct code on our side is but without having to go thru the entire list in a validation field. (if that makes since)

Using validation before in a similar instance i used indirect to grab a named range to help shorten the list for them. However in this case that would be a ton of ranges i would have to set up! I've tried Match, countif and index in an offset formula in the validation list but can never get it to work!

View Replies!   View Related
Using Named Ranges To Update Charts
I want to update a number of charts using data that covers a rolling 12 months - this will be amended each month by addition of new data but I will be retaining the full history. I thought the easiest and quickest way would be to name the data range and just amend it each month as I added new data. However, when I use the named range in the chart wizard it is replaced by direct cell references. Can anyone suggest how I can retain the name in the chart or suggest an easy alternative method?

View Replies!   View Related
Consolidation Named Ranges Into One Worksheet
I've a workbook in excel 2007 with 6 worksheets, 4 of witch have a named ranges, i would like to have a macro that consolidates this named ranges into one worksheet.

Sheet1 -- Skip Worksheet
Sheet2 -- Skip Worksheet
Sheet3 -- Range1
Sheet4 -- Range2
Sheet5 -- Range3
Sheet6 -- Range4


The Named Ranges are Excel 2007 Tables and they are positioned in the same place in Sheet3 to Sheet6.

View Replies!   View Related
Finding Addresses Of Named Ranges
I have what I think is a simple question. I simply want a function that shows the text string address of a named range. If I can do that I can then do a vlookup from another sheet, put the result in an address function, and in effect automatically update dynamic ranges on another sheet.

I swear I have seen this but I can't find it now. Searches result in hundreds of hits, none of them relevant.

To make sure I've explained this properly, I want to put the name of a named range in A1 and have the address of that range appear in A2.

View Replies!   View Related
Advanced Dynamic Named Ranges
I have a spreadsheet with a large number of graphs on and lots of data.

Most the graphs look across 20 or so columns and one or 2 rows down.

The problem is that l need to insert new colums in now, and obviously excel graphs dont recognise the inserted column data.

Therefore l believe using named ranges may solve this problem...

I have the formula =OFFSET(Sheet1!$A$1,0,0,2,MATCH("DC",Sheet1!$1:$1))

However i need to expand on this to achieve what i need to do.

1. Firstly l only want to look from column C onwards (so ignoring all data in column A and B)

2. I need the match to match to columns starting with the letters "DC", however there is a number after this, so it is not a direct match. The match needs to ignore columns with any other heading start.

3. is there anyway to automatically refresh the graphs to the named range once it changes, or any simble vb macro i can run?

View Replies!   View Related
Selecting All Named Ranges At Same Time
does anyone have any suggestions for selecting (highlighting) all the named ranges on a sheet?

View Replies!   View Related
Multiple Dynamic Named Ranges
I am trying to create a dynamic named range with a sales rotation tracker. Basically, I have a list of leads that come in each week and I add them to the tracker distribute the leads to sales people based on a rotation. I would like to create a dynamic named range for each sales person that will select each sales person's name including the lead information in the row.

I have read all of the dynamic named range information at the link below as well as the advanced dynamic named range page and I can't seem to put my finger on this short of doing it manually.

View Replies!   View Related
Named Ranges Keep Changing Values
I have two defined ranges, and they keep changing to include the workbook name. XV2 =

View Replies!   View Related
2007 Delete My Named Ranges
Doesn't happen all the time, and generally happens when a macro is run.

View Replies!   View Related
Copy Named Ranges Only If They Exist
I have the following

wkbDataFile.Worksheets("Admin"). Range("January_Data").Copy Destination:=wb.Worksheets("Admin").Range("January_Data")

In some cases the range "January_Data" won't exist and the macro breaks. Is there a check I can have the macro run through first to ensure the range exists in wkbDataFile.Worksheets("Admin") before going through with the .Copy?

View Replies!   View Related
Networkdays Function & Named Ranges
I have named range in column A = holfrom and another in column B = holtill

When I try to use NETWORKDAYS(holfrom;holtill) I get an error VALUE
Is it even possible to use this function with named ranges

View Replies!   View Related
Format Multiple Named Ranges
Is it possible to use the same code through a number of named ranges without writting it out for each individual range. I have a table with 7 columns and 30 rows, data inputed to the table and then through code manipulaited on font, fill colour, and cell value(some 25 diffrent options) I have produced working code to pull out data as required for column 1 but it is extremly long, is there some code about I can use to loop my code, (automatically changing the named range within my code to cycle through each of my 7 named ranges)? or do I have to write the code out for each individual named range?

View Replies!   View Related
Delete Hidden Named Ranges
I'm trying to delete all the Named Ranges in my excel document, but it appears there are some hidden ranges, and I can't delete them! How do you delete a range that is hidden? If I try to delete it with my code it says "That name is not valid." So I created a message box to see what the name of the range is.....and it's like 30 letters long! It starts like this..."={#N/A,#N/A, TRUE "Summary....." I woud like to attach my Excel file but it's too large. Below is my code

Sub Remove_Hidden_Names()
' Dimension variables.
Dim xName As Variant
' Loop once for each name in the workbook.
For Each xName In ActiveWorkbook.Names
If xName.Visible = True Then
MsgBox (xName)
End If
Next xName
End Sub

View Replies!   View Related
Matching Data With Named Ranges
I have a workbook that I'm trying to figure out. It was made by a previous employee.
This is what the formula looks like =MATCH(B49, OFFSET(Array_Data,,2,,1),0)

Array_Data consists of cells $H$4:$DN$506 on a different worksheet. I've tried to trace the formula but I can't get it to work. I thought I understood the offset function but I've never used it with a named range. Does anyone have a clue on how I can break this down and figure out what cell this data is refering to?

View Replies!   View Related
Named Ranges - Indendify Ones Not Used In Formulas
I have been handed a workbook which has been used and amended by a number of people over about 5 years. The workbook has lots of named ranges, many of which are obviously now defunct. Others may be defunct but I'm not sure. I would like to delete all of the defunct ones.

Does anyone know a way in which I can identify the cells in which a named range is currently being used?

View Replies!   View Related
Copyright © 2005-08, All rights reserved