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

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)

wrote in message
ups.com...
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

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.

--
Regards,
Tom Ogilvy


" wrote:

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

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.C ount + 1250).Address
On Error GoTo 0
Next nam
End Sub

hth,

Doug

wrote in message
ups.com...
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

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

"Doug Glancy" wrote in message
...
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.C ount + 1250).Address
On Error GoTo 0
Next nam
End Sub

hth,

Doug

wrote in message
ups.com...
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

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

Doug Glancy wrote:
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

"Doug Glancy" wrote in message
...
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.C ount + 1250).Address
On Error GoTo 0
Next nam
End Sub

hth,

Doug

wrote in message
ups.com...
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