Changing Range of Defined Name in Excel

Dear All,

I want to set up a script to extend the range of an already defined named array in Excel. The array is a lookup list for a drop down field that I want the user to be able to add to, without leaving unsightly blank lines in the range.

I have created a formula that outputs the range formula of the increased list. How do I adjust the name definition using VB?

And I should probably mention I'm a complete beginner so example code would be a distinct advantage!

Many Thanks in advance!

Skibum

Redimension the array using the preserve keyword to avoid destroying its current contents, and then add the new member to the end.

Metacode=================================

redim preserve arrayName(ubound(arrayName)+1)
arrayName(ubound(arrayName)) = Whatever

========================================

how do you retrieve a named range that is on an excel worksheet?

how do you retrieve a named range that is on an excel worksheet?

I'm not quite sure what you're asking for, but I suspect you might be able to use the CurrentRegion property.

CurrentRegion will return a range of contiguous used cells from a single cell reference.

To test this, try opening a new sheet, typing some text in cell a1, and then filling down to cell a7. Then open the visual basic editor, double-click on the relevant sheet to open its code window, and type:

Sub GetRange()
Dim myRange As Range
Set myRange = Me.Cells(4, 1).CurrentRegion
Debug.Print myRange.Address
Set myRange = Nothing
End Sub

It should return $A$1:$A$7 - the address of the region containing cell A4.

Thanks for the response.. i actually got it to work using the following:
Dim objExcel As Excel.Application
Dim ratiocy As Excel.Worksheet
Dim copiedchart As Object
Dim xlwrksheet As Excel.Worksheet
Dim comprange As Excel.Range
Dim myNamed As Excel.Range
objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open("c:\test.xls")
objExcel.DisplayAlerts = False
objExcel.Visible = False
ratiocy = objexcel.Worksheets(1)
myNamed = ratiocy.Range(layoutvalue(x, 4))
myNamed.Copy()
With objExcel ' Use With...End With so you don't have to type objExcel over and over again

.ActiveWorkbook.Close()
.Quit()
End With
objExcel = Nothing