Hi Wizards,
I managed to write the code below (at the end of the post) to store the visible row numbers in an array. However, before proceeding with every iteration of the outer for loop, i want to randomly choose k rows from the array before flushing it. This number k is calculated as z% of visible rows output by the filter.
For example, if there are 1000 rows in a sheet where filter is applied to get 250 rows, then k = 12.5 (or 13 - always need to take the ceiling value) when z=5.
Now, i want to choose 13 random elements (row numbers) from the array. How can that be done? After randomly choosing the elements, i execute the below piece of code before flushing the array contents and changing the filter (Next x - Outer for loop)
MarkRows:
With Range("A1:H" & Cells(Rows.Count, fCol).End(xlUp).Row)
.Resize(.Rows.Count - 1, 1).Offset(i, fCol).SpecialCells(xlCellTypeVisible) = "Sample_" & x
End With
Sub Filtered_Rows_Test()
Dim ws As Worksheet
Dim val, x, LR, rowsTotal As Integer
Dim rngArray(), i As Long
Dim rngTemp As Range
Set ws = Worksheets("Sheet1")
For x = 1 To 10
ws.Range("A1:H1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="=" & x
LR = ws.Range("A" & Rows.Count).End(xlUp).Row
rowsTotal = ws.Range("A1:A" & LR).SpecialCells(xlCellTypeVisible).Count - 1
ReDim rngArray(1)
'MsgBox "Total number of filtered rows are " & rowsTotal
For i = 1 To LR
If Not Cells(i, "A").EntireRow.Hidden Then
'MsgBox Cells(i, "A").Row
ReDim Preserve rngArray(UBound(rngArray) + 1)
rngArray(UBound(rngArray)) = Cells(i, "A").Row
End If
Next i
Next x
' MarkRows: code is included here with some more code
ws.AutoFilterMode = False
End Sub
Sarang
Bookmarks