PDA

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



vishwakarma
07-14-2013, 01:37 AM
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
07-14-2013, 07:55 AM
Dim Sht as Worksheet
For each Sht is Worksheets
Sht.PrintArea= Sht.UsedRange.Address
Next Sht

vishwakarma
07-14-2013, 09:32 PM
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
07-15-2013, 02:17 AM
What is the error being given?

vishwakarma
07-15-2013, 08:39 AM
It's giving me 'Compile error: Method or data member not found' message.

Thanks,

SamT
07-15-2013, 10:31 AM
My typo error

"Is" = "In"

vishwakarma
07-16-2013, 03:14 AM
Sorry guys.. I don't know what I'm doing wrong..but it is still giving me the same error message.

Aussiebear
07-16-2013, 04:19 AM
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
07-16-2013, 08:25 AM
Manoj,

Sorry about that.

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

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

Sht.PageSetup.PrintArea = Sht.UsedRange.Address

Next Sht

End Sub
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: Range("A1").CurrentRegion


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, Dim PA as String
PA = "A1:X99"
Sht.PageSetup.PrintArea = PA

vishwakarma
07-18-2013, 08:29 AM
Thank You SamT.. This is exactly what I needed.