Name Range Outside Range

[mcpop7]

I have a range in a worksheet named 'myrange' which refers to a block o
cells

I want to name the range that is outside of this, i.e all cells outsid
of 'myrange'

Any suggestions would be warmly received

 

[Bob Phillips]

Here's one way

saveSet = ActiveSheet.UsedRange.Formula
ActiveSheet.UsedRange.ClearContents
Range("A3:A5") = 0
ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Name =
"outsideRange"
ActiveSheet.UsedRange = saveSet

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[mcpop7]

Thanks, but the problem is I need to keep the contents of my rang
intact and the content is not formulae

my range is $A$1:$U$8 named "my_range" but the dimensions of this coul
change. I want to be able to name the range outside of this

I'm trying in essence to do a select 'NOT' "my_range" to return al
cells outside the rang

 

[Tom Ogilvy]

Why not try it on a copy of your data. Formula includes Values.

 

[mcpop7]

I mean, i can do all this:

Set rngFirstRange = Range(Range("a1").End(xlDown).Offset(1, 0)
Range("a1").End(xlDown).Offset(1, 0).End(xlDown))

Set rngFirstRange = Range(rngFirstRange, rngFirstRange.En
(xlToRight))


Set rngSecondRange = Range("a1").End(xlToRight).Offset(0, 1)
Set rngSecondRange = Range(rngSecondRange
rngSecondRange.End(xlDown))
Set rngSecondRange = Range(rngSecondRange
rngSecondRange.End(xlToRight))

Set rngOutsideRange = Application.Union(Range(rngFirstRange)
Range(rngSecondRange))


but isn't there a quicker way?

 

[Tom Ogilvy]

For you, no.

 

[mcpop7]

what do you mean "for me"?, do you mean it's not possible or are yo
being sarcastic

 

[Tom Ogilvy]

I mean if you are not willing to try the solution suggested, then no,
because that is the less complex, less situation dependent, solution. There
is no Not a member of the range built in function.

 

[Alan Beban]

When I tried the "solution" suggested, the range of "outsideRange" was
limited to cells within UsedRange. The OP's code indicates he is trying
to return the range of cells outside of UsedRange (although his code
didn't work for me either; it threw a "Method 'Range' of object
'_Global' failed" error message for the Union command).

Alan Beban