Saarang84
New Member
- Joined
- Jan 4, 2009
- Messages
- 38
Hi Wizards,
Can you help me with a small requirement in a module in my macro in which i'm trying to automate a random selection of rows?
Here's the background..
I've a workbook with 2 sheets, one called CONFIG and another called MASTER. The CONFIG sheet contains a list of names of users and specifies their type alongside.
In the MASTER sheet, there are few columns including a column of names and i've included a random number column using the formula as below:
=INT(RAND()*1000)+1
1000 indicates that there are 1000 rows in the MASTER sheet.
Here's a piece of code i've developed:
My requirement is to randomly choose x% of rows out of the total rows Cleared & Closed by each user. Based on the user type, the value of x varies.
The number of sample rows to be chosen is calculated and rounded-off to the next highest integer using CEILING if decimal value exceeds 0.5
How can random rows be chosen? Can somebody help me?
Sarang
Can you help me with a small requirement in a module in my macro in which i'm trying to automate a random selection of rows?
Here's the background..
I've a workbook with 2 sheets, one called CONFIG and another called MASTER. The CONFIG sheet contains a list of names of users and specifies their type alongside.
In the MASTER sheet, there are few columns including a column of names and i've included a random number column using the formula as below:
=INT(RAND()*1000)+1
1000 indicates that there are 1000 rows in the MASTER sheet.
Here's a piece of code i've developed:
Code:
Sub RandomFilter()
Dim i, x, z, F_Row, L_Row, NameCnt As Integer
Dim vCol, fCol, LR, vCells, UsrType As Long
Dim rngFilter, rngVisible, FilterRange As Range
Dim strUserNames As Variant, Result As Variant
Dim wsM As Worksheet
Application.ScreenUpdating = False
Set wsM = Sheets("MASTER")
With wsM
vCol = .Rows(1).Find("LAST_UPDATE_NAME", LookIn:=xlValues, LookAt:=xlWhole).Column
fCol = .Cells(1, .Columns.Count).End(xlToLeft).Column + 1
LR = .Cells(.Rows.Count, vCol).End(xlUp).Row
NameCnt = Sheets("CONFIG").Range("A65536").End(xlUp).Row
ReDim strUserNames(2 To NameCnt) As Variant
For i = 2 To NameCnt
strUserNames(i) = Sheets("CONFIG").Cells(i, 1).Value
Next i
ReDim Preserve strUserNames(2 To NameCnt) As Variant
.Cells(1, fCol) = "Flag"
.Range(.Cells(2, fCol), .Cells(LR, fCol)).FormulaR1C1 = "=INT(RAND()*" & LR & ")+1"
' Condition is if UsrType is New, then z = 5%, else if UsrType is Existing, then z = 2.5%
For i = 1 To NameCnt
.Columns("A:J" & Cells(Rows.Count, 1)).AutoFilter field:=1, Criteria1:="=" & strUserNames(i)
.Columns("A:J").AutoFilter field:=9, Criteria1:="<" & (z / 100)
vCells = .Cells(.Rows.Count, fCol).End(xlUp).Row
With Range("A1:J" & Cells(Rows.Count, fCol).End(xlUp).Row)
.Resize(.Rows.Count - 1, 1).Offset(i, fCol).SpecialCells(xlCellTypeVisible) = "Sample_" & i
End With
.Columns("A:J" & Cells(Rows.Count, 1)).AutoFilter
Next
End With
End Sub
My requirement is to randomly choose x% of rows out of the total rows Cleared & Closed by each user. Based on the user type, the value of x varies.
The number of sample rows to be chosen is calculated and rounded-off to the next highest integer using CEILING if decimal value exceeds 0.5
How can random rows be chosen? Can somebody help me?
Sarang