Results 1 to 12 of 12

Random Selection of Row Numbers from a Filtered Range

Threaded View

  1. #8
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: Random Selection of Row Numbers from a Filtered Range

    Hi,

    I found the below link by Tushar Mehta on Random Selection

    Here's my code where i'm trying to incorporate some code from the above link.


    Sub RandomFilter()
    Dim p As String
    Dim wsM As Worksheet
    Dim rndRowRng(), rndRowSamples() As Variant
    Dim rngVSR, rngFR As Range
    Dim i, x, rowNum As Integer
    Dim vCol, fCol, LR, vCellCount, randRow, A As Long
    Application.ScreenUpdating = False
    Set wsM = Sheets("TestRandom")
    With wsM
    vCol = .Rows(1).Find("DATA1", LookIn:=xlValues, LookAt:=xlWhole).Column
    fCol = .Cells(1, .Columns.Count).End(xlToLeft).Column + 1
    LR = .Cells(.Rows.Count, vCol).End(xlUp).Row
    .Cells(1, fCol) = "Flag"
    .Range(.Cells(2, fCol), .Cells(LR, fCol)).FormulaR1C1 = "=ROW()-1"
     
    ReDim rndRowRng(2 To LR) As Variant
    ReDim rndRowSamples(2 To LR) As Variant
    For i = 1 To 10
    .Columns("A:J" & Cells(Rows.Count, 1)).AutoFilter field:=1, Criteria1:="=" & i
    If .FilterMode Then
    With .AutoFilter.Range
    Set rngVSR = .Columns(1).Offset(1, 0).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
    End With
    Else
    MsgBox "Filters not Set." & vbLf & "Processing Terminated."
    Exit Sub
    End If
    vCellCount = rngVSR.Cells.Count
    p = InputBox("Enter Sheet Name: ", "User Type")
    Randomize
    If p = "New" Then
    randRow = Int(vCellCount * (5 / 100))
    ElseIf p = "Existing" Then
    randRow = Int(vCellCount * (2.5 / 100))
    End If
    For rowNum = 2 To LR
    rndRowRng(i) = rngVSR.Cells(i, 9).Value
    Next rowNum
    ReDim Preserve rndRowRng(2 To LR) As Variant
     
    ' Invoke Tushar Mehta's code here
     
    For x = LBound(rndRowSamples) To UBound(rndRowSamples)
    With Range("A1:J" & Cells(Rows.Count, fCol).End(xlUp).Row)
    .Resize(.Rows.Count - 1, 1).Offset(i, .Cells(x, 10)).SpecialCells(xlCellTypeVisible) = "Sample_" & i
    End With
    Next x
    Erase rndRowRng
    .Columns("A:J" & Cells(Rows.Count, 1)).AutoFilter
    Next i
    End With
    End Sub

    Using .FormulaR1C1 = "=ROW()-1" shows row numbers in a column.
    After applying filter to DATA1 column in the sheet, i want to read the visible row numbers and store it in an array.

    I'm trying to include the RandomSelect method below (from the above link) to select random row numbers by mixing the (above mentioned) array and mark the selected array elements. The randRow variable contains the number of rows to be chosen from the array.

    Return a specified number of random values from an user specified array

     
    Sub Swap(ByRef Arr() As Variant, ByVal i As Long, ByVal j As Long)
    Dim temp As Variant
    temp = Arr(i): Arr(i) = Arr(j): Arr(j) = temp
    End Sub
     
    Sub RandomSelect(ByRef Arr() As Variant, ByVal N As Long)
    Dim z As Long, Idx As Long
    'Need edits to ensure Arr is an acceptable data type. Similarly, validate n
    For z = 1 To N
    Idx = LBound(Arr) + (z - 1) + Int((UBound(Arr) - (LBound(Arr) + (z - 1)) + 1) * Rnd())
    Swap Arr, LBound(Arr) + (z - 1), Idx
    Next z
    End Sub

    I'm not able to incorporate and invoke the above module. Can someone help me incorporating it??

    Sarang
    Last edited by Saarang84; 09-12-2010 at 11:20 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1