resize a named range

nitind

Board Regular
Joined
Oct 30, 2008
Messages
77
I have a range "A1" named array.Now I want to resize this "array" to include two more rows and keeping only one column.How to do that in VBA.
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,798
Office Version
365
Platform
Windows
Hi

Give this a try:

MsgBox Range("array").Resize(3).Address
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,798
Office Version
365
Platform
Windows
Can you show us your code please?

When you say you have a cell named "array" - do you mean a workbook level named range? If so this should work:
Code:
ActiveWorkbook.Names.Add Name:="array", RefersToR1C1:=Range("array").Resize(3)
Note, this will increase the size of the array by two rows each time the macro is run.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,966
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Code:
Range("A1:A3").Name = "blah"
should work.
 

nitind

Board Regular
Joined
Oct 30, 2008
Messages
77
Can you show us your code please?

When you say you have a cell named "array" - do you mean a workbook level named range? If so this should work:
Code:
ActiveWorkbook.Names.Add Name:="array", RefersToR1C1:=Range("array").Resize(3)
Note, this will increase the size of the array by two rows each time the macro is run.
What if I add the ange name using VBA
 

Watch MrExcel Video

Forum statistics

Threads
1,099,113
Messages
5,466,752
Members
406,496
Latest member
Arlind Elezi

This Week's Hot Topics

Top