Flag Rows Randomly after Applying Filter

[rangudu_2008]

Hi,

Came across this thread while browsing through the Excel Forum:

How to flag records selected by Autofilter (http://www.excelforum.com/excel-programming/740869-how-to-flag-records-selected-by-autofilter.html)

I've an excel sheet of data where i want to apply filter based on a column and from the resultant records from the filter, i want to flag certain no. of rows randomly.

For example, if there are 2500 rows and after filtering, there are say 175 rows. Then, how can a specific % of records (say 5% or 2.5%) be chosen and flagged randomly?

Ranga

[mikerickson]

You could add a helper column of =RAND() and add a filter on that column for < x% to randomly select x% of a filtered range.

[rangudu_2008]

Sample Workbook attached.

[mikerickson]

The attached has the helper column (AW) and a custom AutoFilter applied (value <.5)

[rangudu_2008]

Hi,

I've managed to write a piece code in the attached workbook.

I need some help on the module below and in the Offset part which i want to include in a for loop to flag / mark filtered rows as samples



With Range("C1:C" & Cells(Rows.Count, 3).End(xlUp).Row)
.Resize(.Rows.Count - 1, 1).Offset(1, 1).SpecialCells(xlCellTypeVisible) = "Listed"
End With


Filter needs to be applied to col A and for each number from 1 to n (in this case its upto 10) filter col I for x% of rows (Flag column). I need to flag / mark all the rows that are output by the filter applied to Flag column (In col J, put the word Sample for all the filtered rows randomly).

Just in case, instead of numbers in col A, if names are used (alphanumeric), how can rows be flagged randomly for each string?

The below piece of code (invoked at the click of a button) doesn't work as i intend it to (particularly, the for loop highlighted in Red) Refer code below:



Sub FilterRandom()
Dim i As Integer
Dim vCol, fCol, LR, vCells As Long
Dim wsM As Worksheet
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 = "=RAND()"
For i = 1 To 10
.Columns("A:A" & Cells(Rows.Count, 1)).AutoFilter field:=1, Criteria1:="=" & i
'.Range("A1", .Cells(vCells, vCol)).AutoFilter field:=1, Criteria1:="=" & i
.Columns("A:A" & Cells(Rows.Count, 1)).AutoFilter field:=10, Criteria1:="<" & (5 / 100)
vCells = .Cells(.Rows.Count, fCol).End(xlUp).Row
With Range("A1:J" & Cells(Rows.Count, 9).End(xlUp).Row)
.Resize(.Rows.Count - 1, 1).Offset(i, 10).SpecialCells(xlCellTypeVisible) = "Sample"
End With
Next
End With
End Sub


Ranga