Solved: Change/Update Defined Names 'Ranges' (multiple) in Name Manager

[vishwakarma]

Hi Guys,

Is it possible to change multiple Defined Names Ranges in excel Name Manager.

So, let's say I have defined some names in the excel file but somehow there was a need to change the ranges in defined names. We can update the ranges if we have few but what if we have multiple defined ranges. It will be double the effort and consume a lot of time.

Please advise..

Thanks,

[SamT]

[VBA]Dim Sht as Worksheet
For each Sht is Worksheets
Sht.PrintArea= Sht.UsedRange.Address
Next Sht[/VBA]

[vishwakarma]

Hi Sam,

Thanks for the code. However, I tried to run it but it is giving me an error.

Would you be able to share a working file?

[Aussiebear]

What is the error being given?

[vishwakarma]

It's giving me 'Compile error: Method or data member not found' message.

Thanks,

[SamT]

My typo error

"Is" = "In"

[vishwakarma]

Sorry guys.. I don't know what I'm doing wrong..but it is still giving me the same error message.

[Aussiebear]

Hi Guys,

Is it possible to change multiple Defined Names Ranges in excel Name Manager.

Surely Name Manager gives you that option? And seriously if you need to change the ranges that often then the problem is not in Name Manager but in how the ranges are being constructed or Named. Look deeper into the issue.

[SamT]

Manoj,

Sorry about that.

I wrote the above code in the VBAX editor, and I really, really should have used the VBA editor. (me)

[vba]Sub test()
Dim Sht As Worksheet
For Each Sht In Worksheets

Sht.PageSetup.PrintArea = Sht.UsedRange.Address

Next Sht

End Sub[/vba]
In the VBE, as soon as I put the cursor in "PrintArea" and pressed F1, I saw the answer in a second.

You need to be aware that Excel does not do a very good job of updating UsedRange. If all your sheets have data in in cell "A1" you should instead use: [vba]Range("A1").CurrentRegion[/vba]

The current region is a range bounded by any combination of blank rows and blank columns. Read-only.

If you want to print a specific area, use an address string instead of a sheet range, [vba]Dim PA as String
PA = "A1:X99"
Sht.PageSetup.PrintArea = PA[/vba]

[vishwakarma]

Thank You SamT.. This is exactly what I needed.