Hi Susan
At first i couldn't get what you mean, but i at last get what you were
going to do. thanks. By the way, there are some other ways i can manage
to think of. I think first one is more random but not so efficient than
the other one.

one is

Sub test()
Dim ws1 As Worksheet
Dim VisRng As Range, FnRng As Range
Dim LastRownum As Long, x As Long

Set ws1 = ActiveSheet '<== Change to your Worksheet
With ws1
Set VisRng = .Range("A1", .Cells(Cells.Rows.Count, "A") _
.End(xlUp)).Cells.SpecialCells(xlCellTypeVisible)
LastRownum = .Cells(Cells.Rows.Count, "A").End(xlUp).Row
x = Int(LastRownum * Rnd) + 1
Set FnRng = Intersect(VisRng, .Cells(x, "A"))
Do While (FnRng Is Nothing)
x = Int(LastRownum * Rnd) + 1
Set FnRng = Intersect(VisRng, .Cells(x, "A"))
Loop
MsgBox .Cells(x, "A")
End With
End Sub

another one is

Sub test1()
Dim ws1 As Worksheet
Dim VisRng As Range
Dim co As Long, sel As Long, first As Long

Set ws1 = ActiveSheet '<== Change to your Worksheet

With ws1

Set VisRng = .Range("A1", .Cells(Cells.Rows.Count, "A") _
.End(xlUp)).SpecialCells(xlCellTypeVisible)

co = VisRng.Areas.Count
If VisRng.Areas(co).Cells(1, 1) = "" Then
co = co - 1
End If
sel = Int(co * Rnd) + 1
first = VisRng.Areas(sel).Row
sel = Int((VisRng.Areas(sel).Rows.Count) * Rnd) + first
MsgBox .Cells(sel, "A")

End With

End Sub

keiji

Susan wrote:
Keiji - lol - the range contains some names i am familiar with, so i
hid a bunch of them in alphabetical order & then ran the macro
repeatedly, seeing if any of the hidden names showed up.
susan


On Nov 2, 12:13 am, keiji kounoike <"kounoike AT mbh.nifty.com"
wrote:
I wonder how did you check it returns a value with hidden rows?
a code like this still comes up with hidden rows

s = ws1.Range("a1", ws1.Range("A1").End(xlDown)) _
.Cells.SpecialCells(xlCellTypeVisible).Count
MsgBox s

keiji



Susan wrote:
Dim x As Long
x = Int((ws1.Range("a1", ws1.Range("A1").End(xlDown)) _
.Cells.SpecialCells(xlCellTypeVisible).Count * Rnd) + 1)
this is *supposed* to return a random row out of the visible-only
cells. but it still comes up with the hidden rows. do i have the
SpecialCells designation in the wrong place?
many thanks in advance
:)
susan- Hide quoted text -
- Show quoted text -