Issue with selecting random blank cell in range

Update I have thought of a work around using collections... if anyone can help make this more efficient however that would be brilliant!

Please Login or Register  to view this content.

Can you attach a short Excel sample to be sure we are working on the same data ..!

The code will only really work when the gamezone is completely empty. At the point the range is C4:I10.
once you populate the gamezone the range returned by specialcell blank is a non contiguous one. The although the random number is within the count of cells of that non contiguous range
the .Cells(x) reference is only based on the first area of the non contiguous range. So for some random values this will be in that first area, for others in will be beyond
the first area but within the gamezone area, so it appears okay. for other values it will be outside the gamezone completely.

here is an example. range C4:I10 is empty except for E4.
The range address is $C$4:$D$4,$F$4:$I$4,$C$5:$I$10

If your random number is 3 then the actual cells returned is $C$5 and not $F$4

Here is some code that stores empty cells and then random selects one of them. Once selected it is removed from the collection.

Please Login or Register  to view this content.

Thank you for your help. It seems we had a similar idea in the end! Thank you for explaining the reason for the error!