 |
 |
03-20-2003, 06:33 AM
|
|
Newcomer
|
|
Join Date: Nov 2002
Posts: 3
|
|
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
|
|
03-20-2003, 07:29 AM
|
|
Newcomer
|
|
Join Date: Feb 2003
Posts: 14
|
|
|
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
========================================
|
__________________
I have learned from my mistakes and feel confident that I can repeat them in future.
|
03-21-2003, 12:01 PM
|
|
Freshman
|
|
Join Date: Oct 2002
Posts: 39
|
|
|
how do you retrieve a named range that is on an excel worksheet?
|
|
03-24-2003, 03:14 AM
|
|
Newcomer
|
|
Join Date: Feb 2003
Posts: 14
|
|
Quote:
Originally Posted by Judo Tom
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:
Code:
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. |
__________________
I have learned from my mistakes and feel confident that I can repeat them in future.
|
03-24-2003, 05:52 AM
|
|
Freshman
|
|
Join Date: Oct 2002
Posts: 39
|
|
|
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
|
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
|
| Thread Tools |
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
Similar Threads
|
| Thread |
Thread Starter |
Forum |
Replies |
Last Post |
|
Excel - Selection range problem
|
biafra |
Excel |
1 |
03-19-2003 06:03 AM |
|
Write to another range from an excel function
|
mickboland |
Word, PowerPoint, Outlook, and Other Office Products |
11 |
11-17-2002 08:41 PM |
|
Excel VBA Error: User Defined Type Not Defined
|
EACutter |
Word, PowerPoint, Outlook, and Other Office Products |
1 |
11-15-2002 12:12 AM |
|
best way to access other MS applications
|
internationally |
Word, PowerPoint, Outlook, and Other Office Products |
1 |
10-21-2002 07:51 PM |
|
Naming an Excel range
|
TrentG |
Word, PowerPoint, Outlook, and Other Office Products |
6 |
10-04-2002 07:45 AM |
|
|
|
|
|
 |
|
