Method 'Range' of object '_Worsheet' failed in Union of 3 Ranges

[6afraidbecause789]

Hi Programmers--I think I'm close, but close doesn't count in
programming. Why does this error occur during the worksheet change
event? I must union 3 ranges. Here is the code:

Dim myRange As Range
Dim myIntersect As Range
Dim myCell As Range

Union(Range("Range1"), Range("Range2"), Range("Range3")).Name =
"ComboRange"
Set myRange = Me.Range("ComboRange")
....

Here are the named ranges:

Range1=!$AV$13:!$EZ$13,!$AV$15:!$EZ$15,!$AV$17:!$EZ$17,!$AV$19:!$EZ
$19,!$AV$21:!$EZ$21,!$AV$23:!$EZ$23,!$AV$25:!$EZ$25,!$AV$27:!$EZ$27,!
$AV$29:!$EZ$29,!$AV$31:!$EZ$31

Range2=!$AV$31:!$EZ$31,!$AV$33:!$EZ$33,!$AV$35:!$EZ$35,!$AV$37:!$EZ
$37,!$AV$39:!$EZ$39,!$AV$41:!$EZ$41,!$AV$43:!$EZ$43,!$AV$45:!$EZ$45,!
$AV$47:!$EZ$47,!$AV$49:!$EZ$49

Range3=!$AV$51:!$EZ$51,!$AV$53:!$EZ$53,!$AV$55:!$EZ$55,!$AV$57:!$EZ
$57,!$AV$59:!$EZ$59,!$AV$61:!$EZ$61,!$AV$63:!$EZ$63,!$AV$65:!$EZ$65,!
$AV$67:!$EZ$67,!$AV$69:!$EZ$69

Note: these ranges don't have Sheet2! in them (I want them to be
global--used on every sheet).

Thanks!

 

[Peter T]

Are all ranges to be unioned refer to cells on the same sheet?

Is that sheet the same sheet of your Worksheet change event?

If you answer No to either of the above your code will fail (as written)

I don't understand what you mean by
"these ranges don't have Sheet2! in them"

Named ranges can be at workbook level or Worksheet level, however both types
will refer to cells on a particular sheet

Following has nothing to do with the problem but better to change the
following for when you do get things working

Union(Range("Range1"), Range("Range2"), Range("Range3")).Name =
"ComboRange"
Set myRange = Me.Range("ComboRange")

myRange = Union(Range("Range1"), Range("Range2"), Range("Range3"))
myRange.Name = "ComboRange"

Regards,
Peter T

 

[6afraidbecause789]

Thanks for replying Peter,

The answer to both of these questions is yes.

I tried the code rewrite, but as you said, the problem is something
else. I honestly don't know how to solve this--it's been plaguing me
for over a week.

All I'm trying to do is link together 3 named ranges because Excel
can't handle over 240 characters in one defined named range. My
ranges skip lines, so, they are big.

Any further help would be greatly appreciated.

 

[Peter T]

All I'm trying to do is link together 3 named ranges because Excel

can't handle over 240 characters in one defined named range.

You can't define a name with absolute max 255+ characters in the refersto
string (maybe less), eg with address or using "simplified" the method to
name a range. However the actual limit of a named ranges is between 149 to
224 unique areas, depending on the combination of single/multiple cell areas
but irrespective of the address length.

I really don't know follow the overall objective, and what you have (in
particular with respect to parent sheet of the named ranges and sheet module
of the code, qty of areas per name). Also you haven't said where your code
fails.

try something like this

set r1 = Range("Range1")
set r2 = Range("Range2")
set r3 = Range("Range3")

'long winded way merely to check integrity of each range

set bigRng = Union(r1,r2,r3)

ActiveWorkbook.Names.Add "BigRange", bigRng ' workbook level
or
Me.Names.add "BigRange", bigRng ' sheet level

' sheet level in the sheet module, or change "me" to a ref to the required
sheet

The above way to create the new name should overcome the 255 string limit
but if it fails it's probably due to exceeding the 149-224 limit, check
areas.count of each named range

debug.? r1.areas.count, etc

If you particularly need to overcome the 147-224 limit there is another way.
But better to know the objective first (apart from merely overcoming the
limit)

Regards,
Peter T

 

[6afraidbecause789]

Thanks again Peter--ok, the same error is occuring at
set r1 = Range("Range1")
in the above code.

Any other suggestions?

 

[Peter T]

Does that line work in a normal module

If it works it means Range1 does NOT refer to cells on the same sheet as the
worksheet module (see my first reply in this thread)

If it fails it means the named range is corrupted, possibly due to a #Ref!
error. Try selecting Range1 in the names box left of input bar.

Regards,
Peter T

 

[6afraidbecause789]

Ok, it's working now !@#$
It's working as long as the named ranges are specific to the sheet
(ie., have the sheet's name in the range def like ='Period 2'!$AW
$13:$FB$13,'Period 2'!$AW$15:$FB$15,'Period 2'!$AW$17:$FB$17,'Period
2'!$AW$19:$FB$19,'Period 2'!$AW$21:$FB$21,'Period 2'!$AW$23:$FB
$23,'Period 2'!$AW$25:$FB$25,'Period 2'!$AW$27:$FB$27)

I was just trying to do it so didn't have to make 3 named ranges on 7
different sheets. If it's possible to make 3 global ranges (without
specifying 'Period__'!) for use across multiple sheets, just let me
know.

Your time and advice is very much appreciated.

 

[Peter T]

If I follow what you are trying to do the answer is no. You can't change a
named range to refer to cells on whatever sheet happens to be active
(actually there is a convoluted way but not viable for what you have in
mind).

What you can do is have identically named Worksheet level names. These named
ranges could refer to similar cell references on each sheet. However that
will still mean a set of names for each sheet.

To create a Worksheet level name

ActiveSheet.Names.Add "myName", theRange
Manually, prefix the name with SheetName!, eg

Sheet1!myName ' if it fails embrace the sheet name with apostrophes

Regards,
Peter T