Solved

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

Posted on 2015-02-20
13
50 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
  • 7
  • 6
13 Comments
 
LVL 45

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 45

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
 

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 45

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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 45

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 45

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 45

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 45

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…
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…

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now