cant have a range of 30 different cells ?

[neowok]

basically i have this code

With Target
If Not Intersect(.Cells
Range("$C$7:$E$7,$G$7:$I$7,$K$7:$M$7,$O$7:$Q$7,$S$7:$AC$7," & _
"$AE$7:$AG$7,$AI$7:$AK$7,$AM$7:$AO$7,$AQ$7:$AS$7,$AU$6:$AW$7
$AY$7:$BA$7, $BC$7:$BE$7," & _
"$BG$6:$BI$7, $BK$6:$BM$7, $BO$6:$BQ$7, $BS$6:$BU$7, $BW$7:$BY$7
$CA$7:$CC$7," & _
"$CE$7:$CG$7, $CI$7:$CK$7, $CM$7:$CO$7, $CQ$7:$CS$7, $CU$6:$CW$7
$CV$7:$DA$7," & _
"$DC$6:$DE$7, $DG$7:$DI$7, $DK$7:$DM$7, $DO$7:$DQ$7, $DS$3:$DU$5")) _
Is Nothing Then

theres 30 items in that range, and excel is refusing to allow it, i
just says "method 'range' of object '_worksheet' failed". I tried t
define a named range but excel only seems to allow 9 items in that
clicking a 10th unselects the whole lot.

must be a way i can have this list and be able to use it in the abov
code

 

[Jim Rech]

I think the problem is that the range's definition exceeds 255 characters.
How about breaking this up into sub areas and testing each one until the
intersect is not nothing?

--
Jim Rech
Excel MVP
| basically i have this code
|
| With Target
| If Not Intersect(.Cells,
| Range("$C$7:$E$7,$G$7:$I$7,$K$7:$M$7,$O$7:$Q$7,$S$7:$AC$7," & _
| "$AE$7:$AG$7,$AI$7:$AK$7,$AM$7:$AO$7,$AQ$7:$AS$7,$AU$6:$AW$7,
| $AY$7:$BA$7, $BC$7:$BE$7," & _
| "$BG$6:$BI$7, $BK$6:$BM$7, $BO$6:$BQ$7, $BS$6:$BU$7, $BW$7:$BY$7,
| $CA$7:$CC$7," & _
| "$CE$7:$CG$7, $CI$7:$CK$7, $CM$7:$CO$7, $CQ$7:$CS$7, $CU$6:$CW$7,
| $CV$7:$DA$7," & _
| "$DC$6:$DE$7, $DG$7:$DI$7, $DK$7:$DM$7, $DO$7:$DQ$7, $DS$3:$DU$5")) _
| Is Nothing Then
|
| theres 30 items in that range, and excel is refusing to allow it, it
| just says "method 'range' of object '_worksheet' failed". I tried to
| define a named range but excel only seems to allow 9 items in that,
| clicking a 10th unselects the whole lot.
|
| must be a way i can have this list and be able to use it in the above
| code?
|
|
| ---
| Message posted
|

 

[Tom Ogilvy]

Sub Tester1()
Set Target = Range("M7")
Dim s1 As Range, s2 As Range
Dim s3 As Range, s4 As Range
Dim s5 As Range, rng As Range
With Target
Set s1 = Range( _
"$C$7:$E$7,$G$7:$I$7,$K$7:$M$7,$O$7:$Q$7," & _
"$S$7:$AC$7,$AE$7:$AG$7")
Set s2 = Range( _
"$AI$7:$AK$7,$AM$7:$AO$7,$AQ$7:$AS$7," & _
"$AU$6:$AW$7,$AY$7:$BA$7, $BC$7:$BE$7")
Set s3 = Range( _
"$BG$6:$BI$7, $BK$6:$BM$7, $BO$6:$BQ$7," & _
" $BS$6:$BU$7, $BW$7:$BY$7,$CA$7:$CC$7")
Set s4 = Range( _
"$CE$7:$CG$7, $CI$7:$CK$7, $CM$7:$CO$7," & _
" $CQ$7:$CS$7, $CU$6:$CW$7,$CV$7:$DA$7")
Set s5 = Range( _
"$DC$6:$DE$7, $DG$7:$DI$7, $DK$7:$DM$7," & _
" $DO$7:$DQ$7, $DS$3:$DU$5")
Set rng = Union(s1, s2, s3, s4, s5)
If Not Intersect(.Cells, rng) Is Nothing Then
MsgBox Intersect(.Cells, rng).Address
End If
End With
End Sub