Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

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

 

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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

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…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…
Suggested Courses

604 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