Pick a row randomly for each user

[sunilbsrv2k]

Hi All,

I am having a set of data containing several rows and columns.

This data contains "User Name" in one of the columns; however another column contains the Date on which he accessed a document. One more column has this document ID.

Now, I need to randomly pick the entire row for each of the users but needs to be one row per day per user. It should cover for all the users of that day and paste it in a new sheet.

Request you to kindly help.

Thanks

 

[sunilbsrv2k]

Hi,

I guess I have complicated my question.

In short, I want my code to pick a row randomly based on the username and the date.

Please help.

Thanks,

 

[sunilbsrv2k]

Hi All,
I am trying different method to pick a row randomly.

This time, I am filtering the data for each user and trying to pick a row in that. However, I am stuck at the point of copying the row, as the Rnd function is giving me decimal results. Could you please help.

Sub pickRandomly()
Dim wb, wb1 As Workbook
Dim ws, ws1 As Worksheet
Dim rng, rng1 As Range
Dim i, n As Integer

Set wb = ThisWorkbook

Set ws = wb.Worksheets(1)

Set rng = ws.Range("B10:B17")
Rndm = Rnd(5)
MsgBox Rndm
For Each cell In rng
    'MsgBox cell
    n = 0
    Worksheets("Sheet1").Range("A22").AutoFilter Field:=2, Criteria1:=cell.Value
    i = Application.WorksheetFunction.Subtotal(3, Range("A23:A1000"))
    'MsgBox i
    For n = 1 To i
        If n = Rndm Then
            MsgBox Cells(n + 20, 1).Value
        End If
    Next
    MsgBox n
Next

End Sub

Thanks