(Reverse Union) Randomly draw address from Range

[ExcelMonkey]

I am looking to build a function that does the opposite of a union function.
Put it in a loop that randomly draws a cell from a pre-defined range. And
redefine what that range would look like after the draw.

So for example lets say my pre-defined address is A1:Z400. I want the
ability to randomly draw a single cell address (say H60) and then remove this
address from the pre-defined range of A1:Z400 so that it now look like:

A1:G400, H1:H59,H61:H400,I1:Z400.

Now I dont think I will be able to store this string as a range variable as
I think I have limits on the lenght of the string stored in a Range variable
(254 char). So as I continue to draw cells from the range, the concatenated
string will get longer and longer over time and breach the 254 char limit.

Any ideas on how to approach this?

Thanks

EM

 

[Dave Peterson]

I'm not sure how you're going to choose that cell at random, but I'd start here
for "unselecting" a range:

http://www.cpearson.com/excel/unselect.htm
(from Chip Pearson's site)

 

[Tim Zych]

Here's an modification of the last macro.

Sub RngWithExceptions()
Dim rng As Range
Dim rngArea As Range
Dim rngCell As Range
Dim rngUnion As Range
Dim rngException As Range
Set rng = Range("A1:Z100")
Set rngException = Range("H60")
For Each rngCell In rng.Cells
If Intersect(rngCell, rngException) Is Nothing Then
If rngUnion Is Nothing Then
Set rngUnion = rngCell
Else
Set rngUnion = Union(rngUnion, rngCell)
End If
End If
Next
' rngUnion.Select
For Each rngArea In rngUnion.Areas
Debug.Print rngArea.Address(0, 0, , True)
Next
End Sub

Now I dont think I will be able to store this string as a range variable
as
I think I have limits on the lenght of the string stored in a Range
variable
(254 char). So as I continue to draw cells from the range, the
concatenated
string will get longer and longer over time and breach the 254 char limit.

I would not do it this way, one of the reasons being the limitation you see.
I can't think of a time I have ever concatenated ranges this way, so there
must be a different approach available. However, if you are hell bent on
doing this, you could write a macro, I suppose, that stores your exceptions
in a sheet cell by cell, or area by area with each exception, then loop
through them, create a rngUnionExceptions by unioning them, then refer to
the union exceptions rather than the hardcoded one.

 

[ExcelMonkey]

Thanks again Tim, you have been stellar at returning posts. I am actually
going somewhere with all of this. I will try to create the random draw and
then follow up with a follow-up question.

Regards

EM