?
Solved

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

Posted on 2015-02-20
13
Medium Priority
?
58 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 49

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 49

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 49

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 49

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 49

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 49

Accepted Solution

by:
Martin Liss earned 2000 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 49

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…
Suggested Courses
Course of the Month11 days, 3 hours left to enroll

770 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