Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Location
    Manchester, Gtr Manchester, England
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Select random cells - not random numbers (Excel 2003)

    Is there a way to select 500 random cells from a worksheet where data is spread across multiple rows and columns? Also a lot of cells contain no data and I wouldn't want these included in the randomly selected cells.
    Need to do a verification check (manually) on the data that is returned from the random search.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select random cells - not random numbers (Excel 2003)

    Do you literally want the cells to be selected, or do you want to copy the values of the randomly selected cells to a new worksheet or new workbook?

  3. #3
    New Lounger
    Join Date
    Jun 2002
    Location
    Manchester, Gtr Manchester, England
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select random cells - not random numbers (Excel 2003)

    If there is a way of copying the values of the cells into a new worksheet then that would be ideal.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select random cells - not random numbers (Excel 2003)

    You can use the following macro:

    Public Sub SelectRandomCells()
    Const SampleSize = 500
    Dim rng As Range
    Dim sel As Range
    Dim cel As Range
    Dim r As Long
    Dim r2 As Long
    Dim c As Long
    Dim c2 As Long
    Dim n As Long
    Set rng = ActiveSheet.UsedRange
    r2 = rng.Rows.Count
    c2 = rng.Columns.Count
    Worksheets.Add
    Do While n < SampleSize
    r = Int(1 + Rnd * r2)
    c = Int(1 + Rnd * c2)
    Set cel = rng.Cells(r, c)
    If Not cel.Value = "" Then
    If sel Is Nothing Then
    Set sel = cel
    n = 1
    Cells(n, 1) = cel
    ElseIf Intersect(sel, cel) Is Nothing Then
    Set sel = Union(sel, cel)
    n = n + 1
    Cells(n, 1) = cel
    End If
    End If
    Loop
    End Sub

    Notes:

    1) You can replace UserRange with a specific range if necessary.
    2) If the range contains fewer than SampleSize non-blank cells, the macro will go into an endless loop.
    3) If the number of non-blank cells is only slightly higher than SampleSize, the macro will take a long time to complete.

  5. #5
    New Lounger
    Join Date
    Jun 2002
    Location
    Manchester, Gtr Manchester, England
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select random cells - not random numbers (Excel 2003)

    Hans - You're a star - thank you so much for that, that has worked a treat!

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select random cells - not random numbers (Excel 2003)

    Might I suggest adding the following right after setting the rng variable to eliminate the possibility of an infinate loop:

    <code>
    If rng.Cells.Count > SampleSize Then
    MsgBox "SampleSize greater than used cells count.
    Exit Sub
    End If
    </code>
    Legare Coleman

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select random cells - not random numbers (Excel 2003)

    Good idea. Could be extended to

    If rng.Cells.Count - rng.SpecialCells(xlCellTypeBlanks).Count > SampleSize Then

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •