How to update (automatically) the range of a Defined name range

**chamdan** · 10-27-2008, 02:30 PM

Need help!

I am looking for a Macro that will allow me to update the range of a Defined name range such as every time I add a name into the list, the only thing I will be doing is passing the client name and the list of client will be automatically updated and my drop down ListBox will show the new client name, which can be selected by the user.

Thank you in advance for your help.

Chuck

**Kenneth Hobson** · 10-27-2008, 04:57 PM

Not sure I know what you mean.

To update a list from A2 to the first empty cell down:

Please Login or Register  to view this content.

If I want to update a named range from E2 to the last cell with a value:

Please Login or Register  to view this content.

**chamdan** · 10-28-2008, 01:07 PM

Hi Kenneth,

Let me thank you first for taking the time to reply to my request but this is not exactly what I am looking for. Let me try to explain:

I have Data validation list which range from let us say D100:D200, whose Defined name range is named SupplierList. Now when the user click on the Dropdown list, which is created originally using the Data Validation List and the Supplier is not listed, whatever the user enter will be added to the list of supplier and therefore the range will no longer be D100:D200 but D100:D201, which will be reflected in the Defined Name range called SupplierList with the new range.

Does this makes more sense?

Thanks again.

**Kenneth Hobson** · 10-28-2008, 01:19 PM

What cell(s) would the data be entered? A data validation would fail if the entry does not exist. Maybe a combobox would be a better option? We could do it with a combobox.

**chamdan** · 10-28-2008, 01:52 PM

Then let us use a combo box instead. I will do the necessary changes to use the ComboBox instead, but will need the programming behind the scene that will do the necessary update. I will need the routine that will apply the update when I enter into the Combo Box a new Supplier, which will also update the SupplierList Defined range Name and its range. Thanks for your help.

**royUK** · 10-29-2008, 05:20 AM

If you use a Dynamic Named Range the range will expand as data is entered. See

http://www.cpearson.com/excel/named.htm

**chamdan** · 10-29-2008, 11:15 AM

Thanks RoyUK and Kenneth, I have enclosed here an example so you may understand what I am trying to do. This is something similar to what I am trying to do.

Regards,

Chuck

**royUK** · 10-29-2008, 11:30 AM

Use a Dynamic named Range as I said before

**chamdan** · 10-29-2008, 12:08 PM

Sorry RoyUK, I could not figure out how to make it work. I have checked the example from the link you sent me but could not figure out how to put it together so it can work. I am not an expert in Excel just basic knowledge so bear with me. Would you mind making the necessary update to the spreadsheet I enclosed earlier so I can see how you would do it to work. I could not enter and new value into the combo box and update the range list. Every time I enter a value and try to press <Enter> nothing happens.

Thanks in advance.

Chuck

**Andy Pope** · 10-29-2008, 12:20 PM

Dynamic named ranges do not work well with Acitve Controls.

Use the Define names dialog to alter the SupNames range to

=OFFSET(Sheet1!$A$30,0,0,COUNTA(Sheet1!$A:$A)-1,1)

If you save, close and reopen the workbook the dropdown list will match.

Add new items or remove existing ones and the control will not update correctly.

If you use the built-in data validation with SupNames as the list source it will be dynamic.

**chamdan** · 10-29-2008, 12:49 PM

Hi Andy,

Thanks for your help but this works fine if you update the Range Name but I am wondering if from the Active Control (Combo Box) you can enter a value and this value will be added to the Range. I tried and tried and tried but will not work.

Thanks,
Chuck

**Andy Pope** · 10-29-2008, 01:02 PM

You would need to code against the comboboxes events in order to add new items to the sheet.

If you do that then I think that simply updating the ListFillRange will keep the combobox and list in sync.

So, set the Listfillrange to A30:A39, or what ever the current range of the list is.
Then add this code to the comboboxes event

Please Login or Register  to view this content.

**royUK** · 10-29-2008, 01:03 PM

Is this what you want? I have added the Dynamic Named Range and some code. If you type into the combobox then exit it by clicking a cell the contents are added

Please Login or Register  to view this content.

**chamdan** · 10-29-2008, 01:21 PM

Excellent! This is exactly what I wanted Andy. But now, every time I enter a value my value is added at the very last address of the range and therefore leaving blank rows in between. It should find the last non blank cell and add afterward the new one. Otherwise, This is exactly what I am looking for.

Thanks to all.

Chuck

**chamdan** · 10-29-2008, 01:55 PM

Thank Andy!

It works perfectly and I have added a sort section to it to list in alphabetic order the list found within the range.

Thank you again.
Chuck

**shg** · 10-29-2008, 02:48 PM

chamdan, for the future:

To mark a thread as solved:

Click the Edit button on your first post in the thread

Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes

If more than two days have elapsed since your first post, ask a moderator to mark it.