Re: return random value from visible cells

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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 <shg.3i9...@xxxxxxxxxxxxxxx> 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/showthread.php?t=23642

.

Relevant Pages