# randbetween excluding multiple values

#### justinp2

##### New Member
Hello.

I've found a VBA script that will randomly select a value from within a given range, and will exclude a range of values.

I am trying to modify it to exclude two ranges of values, but am pretty lost. Ideally what it will do is return an integer from the given range "excluding" the values given in Exclude1 and Exclude2.

Could someone help me tweak this?

Code:
``````Function RandBetweenInt(Lowest As Long, Highest As Long, Exclude1 As Range, Exclude2 As Range) As Long
Dim R As Long
Dim C As Range

Do
R = Lowest + Int(Rnd() * (Highest + 1 - Lowest))
For Each C In Exclude1
If R = C Then Exit For
Next C
Loop Until C Is Nothing

RandBetweenInt = R
Application.Volatile
End Function``````

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### CodeNinja

##### Well-known Member
Try something like:

Code:
``````Function RandBetweenInt(Lowest As Long, Highest As Long, Exclude1 As Range, Exclude2 As Range) As Long
Dim R As Long
Dim C As Range
Dim JoinRange As Range

Set JoinRange = Union(Exclude1, Exclude2)

Do
R = Lowest + Int(Rnd() * (Highest + 1 - Lowest))
For Each C In JoinRange
If R = C Then Exit For
Next C
Loop Until C Is Nothing``````

#### JoeMo

##### MrExcel MVP
To avoid the need to loop through two exclusion ranges this might be faster if those ranges are extensive.
Code:
``````Function RandBetweenInt(Lowest As Long, Highest As Long, Exclude1 As Range, Exclude2 As Range) As Long
Dim R As Long, n As Long
Do
n = n + 1
R = Lowest + Int(Rnd() * (Highest + 1 - Lowest))
If Application.CountIf(Exclude1, R) = 0 And _
Application.CountIf(Exclude2, R) = 0 Then Exit Do
Loop Until n = 1000 'can remove the n=1000 part, just here to avoid if everything between lo & hi is excluded
RandBetweenInt = R
Application.Volatile
End Function``````

#### justinp2

##### New Member
Thank you both of these worked well.

I have a tangentially related question but will start a new thread.
Justin

Replies
5
Views
118
Replies
2
Views
198
Replies
2
Views
225
Replies
1
Views
170
Replies
4
Views
108

1,195,831
Messages
6,011,843
Members
441,650
Latest member
ceyoung75

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back