Solved

How do I visual basic to update a named range of cells?

Posted on 2015-02-20
13
54 Views
Last Modified: 2015-02-23
Hello,

I would like to know what is the visual basic code to update the range of cells associated with a named range?

Thanks.
0
Comment
Question by:f19l
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40621273
Why do you need to use Visual Basic to update the named range?
0
 

Author Comment

by:f19l
ID: 40621302
I use a macro to load data into a spreadsheet that has a named range. I then manipulate the data but this has the affect of changing the named range. I would like to ensure that the range is always the same and so I need to use VB to fix the range.
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40621454
So I assume you are saying that you want the named range to be dynamic. If that's the case then using a flexible formula in the named range will enable that to happen. Here are two examples of such a formula. Replace "Sheet Name" and column "A" to match your named range.

Range has no heading
=OFFSET('Sheet Name'!$A$1,0,0,COUNTA('Sheet Name'!$A:$A),1)

Range has a heading
=OFFSET('Sheet Name'!$A$2,0,0,COUNTA('Sheet Name'!$A:$A)-1,1)
(the A$2 is the first cell in the range)

Legend (for 'Range has a heading'
      •      'Sheet Name'!$A$2 - The referenced cell.
      •      0 - Indicates the number of rows to move. Positive numbers mean move down, and negative numbers mean move up.
      •      0 - Indicates the number of columns to move. Positive numbers mean move to the right, and negative numbers mean move to the left.
      •      COUNTA('Sheet Name'!$A:$A)-1- (Optional.) Indicates how many rows of data to return. This number must be a positive number.
      •      1  - (Optional.) Indicates how many columns of data to return. This number must be a positive number.

=OFFSET(C2,2,-1,1,1)
      •      C2 - The referenced cell.
      •      2 - Indicates the number of rows to move. Positive numbers mean move down, and negative numbers mean move up.
      •      -1 - Indicates the number of columns to move. Positive numbers mean move to the right, and negative numbers mean move to the left.
      •      1 (second to last value) - (Optional.) Indicates how many rows of data to return. This number must be a positive number.
      •      1 (last value) - (Optional.) Indicates how many columns of data to return. This number must be a positive number.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:f19l
ID: 40621720
Currently I have set my named range to be "A:AZ" and called it "RANGE". As part of the macro function I add a column in A which has the effect of shifting "RANGE" right by one so that it now refers to "B:BA". I have tried to understand how I would go about implementing your code into my VB code but I am not sure how to do it.
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40622165
If what you are doing is adding a new column on the left then try this.

Sub UpdateNamedRangeAddress()
    
Dim nmMyNamedRange As Name

Set nmMyNamedRange = ActiveWorkbook.Names.Item("Range")

' Replace the first "B" in the address with "A"
nmMyNamedRange.RefersTo = Replace(Range("Range").Address, "B", "A", , 1)

End Sub

Open in new window

0
 

Author Comment

by:f19l
ID: 40625358
When I use your code my named range disappears from the drop down range name box.

The first time I create the range and check how it appears in the name manager I see the Refers To section showing ="Sheet Name!$A:$AZ".

After I use your code the following shows ="$A:$BA".

How do I ensure that the sheet name remains and that the range is returned to A:AZ?
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40625724
Sub UpdateNamedRangeAddress()
    
Dim nmMyNamedRange As Name
Dim strParts() As String

Set nmMyNamedRange = ActiveWorkbook.Names.Item("Range")

'Debug.Print "Named Range before " & nmMyNamedRange.RefersTo

strParts = Split(nmMyNamedRange.RefersTo, "!")
' strParts(0) contains the sheet name and the exclamation point
' strParts(1) contains the range address
' Replace the first "B" in the address with "A"
nmMyNamedRange.RefersTo = strParts(0) & "!" & Replace(strParts(1), "B", "A", , 1)

'Debug.Print "Named Range address after " & nmMyNamedRange.RefersTo

End Sub

Open in new window

0
 

Author Comment

by:f19l
ID: 40625982
I think we are almost there now. What would be the code to ensure that the range always ends up staying as $A:$AZ and does not continually increase by one column each time I run the macro?
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40626035
If you add a new column "A" and then run the above code it will change the formula back to  $A:$AZ . So you should run the code every time you add a new column.
0
 

Author Comment

by:f19l
ID: 40626060
Actually, every time I run the macro the same problem keeps happening: the range change one column to the right each time. I start with A:AZ then it changes to A:BA and then A:BB and so on.
0
 
LVL 47

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 40626087
I see now that I misunderstood what you wanted. I thought that you wanted to expand the range to include the new column, but I assume now that you want to keep the range no matter how many columns you add. If that's a a correct assumption then just do this.

Sub UpdateNamedRangeAddress()
    
Dim nmMyNamedRange As Name
Dim strParts() As String

Set nmMyNamedRange = ActiveWorkbook.Names.Item("Range")

strParts = Split(nmMyNamedRange.RefersTo, "!")
' strParts(0) contains the sheet name
nmMyNamedRange.RefersTo = strParts(0) & "!$A:$AZ"

End Sub

Open in new window

0
 

Author Comment

by:f19l
ID: 40626103
That's it! Thanks.
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40626110
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question