Hi,

I tried implementing the below algorithm for sampling out rows of data randomly. I understood the workflow of the knowledge base code and tried to use it but to no avail. Need some help with accessing the range part correctly and passing it to the Sample function. Since i apply filter through the code, i need to determine the filtering range, manipulate it correctly and then invoke it. The function coded by patrick mathews fulfills my requirement exactly.

My sampling algorithm is as below:

1. As i need x rows marked randomly from each time the filter changes on the below lines of code (refer code in the workbook attached in the first post)

[vba]For i = 1 To 10
.Columns("A:J" & Cells(Rows.Count, 1)).AutoFilter field:=1, Criteria1:="=" & i[/vba]

I intended to use an array and to read and input the row numbers into it (instead of using random values generated by RAND() function)

In other words, instead of the below line:

[vba].Range(.Cells(2, fCol), .Cells(LR, fCol)).FormulaR1C1 = "=RAND()"[/vba]

I thought of changing it as below to populate row numbers at the end of each row of data:

[vba].Range(.Cells(2, fCol), .Cells(LR, fCol)).FormulaR1C1 = "=ROW()-1"[/vba]

This would generate and populate the row number against each row [instead of RAND()] under the column titled Flag. Then this column should be value-pasted.

2. Now, search for this Flag column and read these row numbers for all visible rows (in filter) during each iteration of the for loop after applying filter and store these row numbers in the array.

3. I know little bit terms with array coding and Redim may need to be used inbetween somewhere. Randomize and choose the row numbers from the array and choose the required number of rows to be marked as samples (the required number of rows to be marked as samples is calculated and stored in the variable randRow). I found Tushar's code which may be used or the KB code in the above post i made.

4. Mark the randomly chosen rows as samples using the below line code (needs to be tweaked a bit):

[vba].Resize(.Rows.Count - 1, 1).Offset(i, fCol).SpecialCells(xlCellTypeVisible) = "Sample_" & i[/vba]

5. After marking the sample rows chosen, flush the contents of the array before starting the next iteration where the filter is changed.

6. Proceed with next iteration of the for loop. Process steps 1 to 5 again.

The only thing i'm concerned about implementing the above steps is to use memory efficiently since arrays are used. I've limited knowledge for coding with arrays, can anybody who can play around coding with arrays help me out to implement this algorithm??


Sarang