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

On Oct 31, 10:47*am, 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

does anybody have any ideas? last call.............
:)
susan

On Oct 31, 10:47*am, 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

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(xlCellTy peVisible)
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

--------------------


--
shg
------------------------------------------------------------------------
shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=23642

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


On Nov 1, 3:46*pm, shg wrote:
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(xlCellTy peVisible)
* 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

--------------------

--
shg
------------------------------------------------------------------------
shg's Profile:http://www.thecodecage.com/forumz/member.php?userid=13
View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=23642

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

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

--------------------


--
shg
------------------------------------------------------------------------
shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=23642

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 -

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


On Nov 2, 11:45*am, shg wrote:
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

--------------------

--
shg
------------------------------------------------------------------------
shg's Profile:http://www.thecodecage.com/forumz/member.php?userid=13
View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=23642

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 -