return random value from visible cells

[Susan]

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

 

[Susan]

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

 

[Susan]

does anybody have any ideas? last call.............

susan

 

[Susan]

hmmmmm... interesting. i didn't realize it wouldn't work. i will try
it the way you've described.
thanks a lot for your help!!!

susan

 

[keiji kounoike]

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]

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

 

[Susan]

the range is not large - only 66 rows - so the version you posted
initially worked fine.
thanks so much for your help!

susan

 

[keiji kounoike]

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