hey!!!!! wonderful! thanks a lot bernie!!!!
:)
susan


On Feb 20, 2:00*pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Try

Randomize
iRnd = Int(rVis.Count * Rnd) + 1

HTH,
Bernie
MS Excel MVP

"Susan" wrote in message

...



i have a sub that chooses a random book of the bible (list of books is
on Sheet1) & inserts the name of the book in a textbox. *after that
book has been chosen, the row gets hidden so it doesn't get chosen
again.

i open the spreadsheet & press the "select" button which runs the
sub. *i get (after pressing the button 3x):
1. *Acts
2. *Philippians
3. *Jonah

ok, fine. *close the workbook without saving (so those rows don't get
hidden). *open the workbook, and press the button 3x again. *i get:
1. *Acts
2. *Philippians
3. *Jonah.

so the response is technically random, but not really random. *any
ideas? *i can live with it the way it is, but i'm just curious as to
why i don't get different books the 2nd time i press the button 3x.
(at this point most of the books are visible.) *the pertinent code
follows:
=========================
Private Sub cmdSelect_Click()
Dim ws1 As Worksheet
* Dim rVis As Range
* Dim iRnd As Long
* Dim iRow As Long
* Dim cell As Range
Dim RandomBook As String
Dim myBook As Range

Set ws1 = ActiveWorkbook.Worksheets("Sheet1")

'find a random bible book from column A
'from the visible cells only (ones not chosen
'previously)
Set rVis = ws1.Range("A1", ws1.Range("A1").End(xlDown)) _
* .SpecialCells(xlCellTypeVisible)
iRnd = Int(rVis.Count * Rnd) + 1

For Each cell In rVis
*iRow = iRow + 1
*If iRow = iRnd Then Exit For
*Next cell

iRow = cell.Row
Set myBook = ws1.Range("a" & iRow)

RandomBook = myBook.Value

'enter the bible book in the textbox
Me.txtBook.Value = RandomBook

myBook.EntireRow.Hidden = True

End Sub
==============================
thanks in advance for any insight.
:)
susan- Hide quoted text -

- Show quoted text -