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

Why do you need to use Visual Basic to update the named range?

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.

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.

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.

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

Select allOpen in new window

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?

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

Select allOpen in new window

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?

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.

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.

That's it! Thanks.

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