Programatically increase the size of all named ranges

[ashwinv]

Hi,

I have about 25+ named ranges in my excel workbook. All of these ranges
currently end at row 250 and I want to increase this to 1500 for all
ranges. How do I do this programatically?

Any help is deeply appreciated.

Thanks,
Ashwin

 

[Bob Phillips]

Sub IncreaseRanges()
Dim nme As Name
Dim rng As Range

For Each nme In ActiveWorkbook.Names
On Error Resume Next
Set rng = Range(nme.RefersTo)
On Error GoTo 0
If Not rng Is Nothing Then
Set rng = rng.Resize(150 - rng.Row + 1)
ActiveWorkbook.Names.Add Name:=nme.Name, RefersTo:=rng.Address(,
, , True)
Set rng = Nothing
End If
Next nme
End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Assuming they are all workbook level names and you want to change them all:
Sub ResizeNames()
Dim nm as Name, rng as Range
for each nm in ActiveWorkbook.names
set rng = nm.RefersToRange
set rng = rng.Resize(250)
rng.Name = nm.name
Next
End Sub

Test this on a copy of your workbook to guard against unsuspected results.

 

[Doug Glancy]

Ashwin,

Back it up before trying this:

Sub test()
Dim nam As Name
Dim new_range As Range

For Each nam In ActiveSheet.Names
'in case the name doesn't refer to a range
On Error Resume Next
nam.RefersTo = "=" &
nam.RefersToRange.Resize(nam.RefersToRange.Cells.Count + 1250).Address
On Error GoTo 0
Next nam
End Sub

hth,

Doug

 

[Doug Glancy]

Ashwin,

This assumed that they are all worksheet level names, which is probably not
true. For workbook level names change "ActiveSheet.Names" to
"ActiveWorkbook.Names". Or run it with both if you have a mix.

hth,

Doug Glancy

 

[ashwinv]

Thanks all for your replies.

Doug - I tried your solution and it works. The first name that it picks
up is the entire worksheet itself even though there is no range defined
like that. After I skip that this the program works.

Thanks,
Ashwin