Named Ranges changing references with VBA

[Sanya]

Hi,
I have worbook with plenty of defined names, each for different sheet. Now i want to increase the range of each of them (they should contain two columns more than they already do). Is there any way that I give list to VBA to go through each of them and adds two more columns?

 

[JustynaMK]

Hi Sanya,

Please try to use the following code:

Sub AdjNamedRanges()
    Dim nRange          As Name
    Dim strName         As String
    
For Each nRange In ActiveWorkbook.Names
    strName = nRange.Name
    With ActiveWorkbook.Names.Item(strName)
        .RefersTo = .RefersToRange.Offset(0, 2)
    End With
Next nRange

End Sub

It will loop through all Named Ranges in your Active Workbook and increase each address reference by two columns.

 

[My Aswer Is This]

When I tried this script it moved the range two columns to the right.
Did not increase the range by two columns

I was earlier trying to come up with a answer. And was hoping yours would work.

 

[JustynaMK]

Sorry guys, I totally misunderstood the question.

How about:

Sub AdjNamedRanges()
    Dim nRange          As Name
    Dim strName         As String
    Dim lngRow1         As Long
    Dim lngRow2         As Long
    Dim lngCol1         As Long
    Dim lngCol2         As Long
    Dim r1, r2, c1, c2  As Long
    
For Each nRange In ActiveWorkbook.Names
    strName = nRange.Name
    With ActiveWorkbook.Names.Item(strName)
        lngRow1 = InStr(.RefersToR1C1, "R")
        lngRow2 = InStr(.RefersToR1C1, ":R")
        lngCol1 = InStr(.RefersToR1C1, "C")
        lngCol2 = InStr(lngCol1 + 1, .RefersToR1C1, "C")
        r1 = Mid(.RefersToR1C1, lngRow1 + 1, lngCol1 - lngRow1 - 1)
        c1 = Mid(.RefersToR1C1, lngCol1 + 1, lngRow2 - lngCol1 - 1)
        r2 = Mid(.RefersToR1C1, lngRow2 + 2, lngCol2 - lngRow2 - 2)
        c2 = Right(.RefersToR1C1, Len(.RefersToR1C1) - lngCol2)
        .RefersTo = .RefersToRange.Resize(r2 - r1 + 1, c2 - c1 + 3)
    End With
Next nRange

End Sub

 

[My Aswer Is This]

Justy:

Here I think I found a answer after modifying your script:

Sub AdjNamedRanges_New()
    Dim nRange          As Name
    Dim strName         As String
    Dim ans As Long
For Each nRange In ActiveWorkbook.Names
    strName = nRange.Name
    With ActiveWorkbook.Names.Item(strName)
    ans = Range(strName).Columns.Count
        
        .RefersTo = .RefersToRange.Resize(, ans + 2)
    
    End With
Next nRange
End Sub

 

[JustynaMK]

Nice, looks much better!

 

[Sanya]

Hi, guys,
Thank you very much for a solutions. I have't really defined the problem properly. I don't want to increase all the ranges in the workbook, but half of them, so I tried to twick the macro, but it doesnt work. Can anyone point where the problem may be (i higlighetd my additions):

Sub AdjNamedRanges_New()
Dim nRange As Name
Dim strName As String
Dim ans As Long
Dim MyOnes As String

For Each nRange In ActiveWorkbook.Names
For x = 3 To 35
MyOnes = Cells(x, 40)
If nRange = MyOnes Then

strName = nRange.Name
With ActiveWorkbook.Names.Item(strName)
ans = Range(strName).Columns.Count

.RefersTo = .RefersToRange.Resize(, ans + 2)

End With

End If
Next x

Next nRange
End Sub

 

[My Aswer Is This]

I don't understand.

You said in your original post:
I have workbook with plenty of defined names, each for different sheet. Now i want to increase the range of each of them


But now you say you only want to:
"I don't want to increase all the ranges in the workbook, but half of them"

Well what half ???

I don't understand from just looking at your modified script what you want.
_{^{<strike>
</strike>}}

 

[Sanya]

I don't want to increase size of all the named ranges in workbook.

I want to increase size of all soome the named ranges in workbook. I have the list of the range names that need to be increased