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

 

[shg]

You can't index a multi-area range like that. At the moment, I don't see
how to do this without a loop:

Code:
--------------------
Dim iRow As Long
Dim rVis As Range
Dim cell As Range
Dim iRnd As Long


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

 

[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

 

[shg]

Susan,

Here's a cleaned-up version that should be much faster if the range is
large:

Code:
--------------------
Function NthCell(r As Range, n As Long) As Range
' Returns the range of the nth cell in a range r that may be non-contiguous
Dim rArea As Range
Dim iPos As Long

For Each rArea In r.Areas
If iPos + rArea.Count >= n Then Exit For
iPos = iPos + rArea.Count
Next rArea

Set NthCell = rArea(n - iPos)
End Function

Sub test()
Dim r As Range
Dim n As Long

Set r = Range("B2:B10000").SpecialCells(xlCellTypeVisible)
n = Int(Rnd() * r.Count) + 1

MsgBox NthCell(r, n).Address
End Sub

 

[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