Random Selection of Row Numbers from a Filtered Range

Hi,

I'm trying to get only the row numbers from a filtered range. These row numbers should be stored in an array which will be used later in the code and should exclude the header row.

Below is the code that i tried out. There are 2 different types of msgbox statements which i tried (refer msgbox statements just before the end Sub statement) alternatively (by commenting one of them at a time).

rngVisible.Address gives me the entire filtered range
rngVisible.Row gives me the only one individual row number (1st row number in the filtered range)

Sub Filtered_Rows()
Dim rngFilter As Range
Dim rngVisible As Range
'is there an autofilter on sheet1?
If Sheet1.AutoFilterMode Then
    'are any filters being used?
    If Sheet1.FilterMode Then
        'get a reference to the autofilter range
        'excluding the header row
        With Sheet1.AutoFilter.Range
            Set rngFilter = .Offset(1).Resize(.Rows.Count - 1, .Columns.Count)
        End With
    End If
End If
If rngFilter Is Nothing Then
    MsgBox "no filtering is being applied!"
Else
    'find the visible cells, if there are none then an error will be raised
    On Error Resume Next
    Set rngVisible = rngFilter.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    If rngVisible Is Nothing Then
        MsgBox "there are no visible cells!"
    Else
    ' below msgbox statements to be used alternatively one at a time
        MsgBox rngVisible.Row
        MsgBox rngVisible.Address
    End If
End If
End Sub

How to get only the visible row numbers and store them in an array??
Sample workbook attached.

Sarang

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

You need to process each area in the visible range. Here is some code to count the number of rows. Insert it in your last else block. Once you get the count you can redim an array and load whatever you want into it.

        
        Dim ar As Range
        Dim n As Long
        n = 0
        For Each ar In rngVisible.Areas
            n = n + ar.Rows.Count
        Next ar
        MsgBox n

Why don't you use advancedfilter with xlfiltercopy ?

Yes by using advancedfilter: all 'visible rows' will be displayed in an unbroken range.

Hi,

I found the below link by Tushar Mehta on Random Selection

Here's my code where i'm trying to incorporate some code from the above link.

Sub RandomFilter()
Dim p As String
Dim wsM As Worksheet
Dim rndRowRng(), rndRowSamples() As Variant
Dim rngVSR, rngFR As Range
Dim i, x, rowNum As Integer
Dim vCol, fCol, LR, vCellCount, randRow, A As Long
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 = "=ROW()-1"
 
ReDim rndRowRng(2 To LR) As Variant
ReDim rndRowSamples(2 To LR) As Variant
For i = 1 To 10
.Columns("A:J" & Cells(Rows.Count, 1)).AutoFilter field:=1, Criteria1:="=" & i
If .FilterMode Then
With .AutoFilter.Range
Set rngVSR = .Columns(1).Offset(1, 0).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
End With
Else
MsgBox "Filters not Set." & vbLf & "Processing Terminated."
Exit Sub
End If
vCellCount = rngVSR.Cells.Count
p = InputBox("Enter Sheet Name: ", "User Type")
Randomize
If p = "New" Then
randRow = Int(vCellCount * (5 / 100))
ElseIf p = "Existing" Then
randRow = Int(vCellCount * (2.5 / 100))
End If
For rowNum = 2 To LR
rndRowRng(i) = rngVSR.Cells(i, 9).Value
Next rowNum
ReDim Preserve rndRowRng(2 To LR) As Variant
 
' Invoke Tushar Mehta's code here
 
For x = LBound(rndRowSamples) To UBound(rndRowSamples)
With Range("A1:J" & Cells(Rows.Count, fCol).End(xlUp).Row)
.Resize(.Rows.Count - 1, 1).Offset(i, .Cells(x, 10)).SpecialCells(xlCellTypeVisible) = "Sample_" & i
End With
Next x
Erase rndRowRng
.Columns("A:J" & Cells(Rows.Count, 1)).AutoFilter
Next i
End With
End Sub

Using .FormulaR1C1 = "=ROW()-1" shows row numbers in a column.
After applying filter to DATA1 column in the sheet, i want to read the visible row numbers and store it in an array.

I'm trying to include the RandomSelect method below (from the above link) to select random row numbers by mixing the (above mentioned) array and mark the selected array elements. The randRow variable contains the number of rows to be chosen from the array.

Return a specified number of random values from an user specified array

 
Sub Swap(ByRef Arr() As Variant, ByVal i As Long, ByVal j As Long)
Dim temp As Variant
temp = Arr(i): Arr(i) = Arr(j): Arr(j) = temp
End Sub
 
Sub RandomSelect(ByRef Arr() As Variant, ByVal N As Long)
Dim z As Long, Idx As Long
'Need edits to ensure Arr is an acceptable data type. Similarly, validate n
For z = 1 To N
Idx = LBound(Arr) + (z - 1) + Int((UBound(Arr) - (LBound(Arr) + (z - 1)) + 1) * Rnd())
Swap Arr, LBound(Arr) + (z - 1), Idx
Next z
End Sub

I'm not able to incorporate and invoke the above module. Can someone help me incorporating it??

Sarang

hi everyone,

This is question is cross posted at both the below links (& maybe others...):
http://www.thecodecage.com/forumz/ex...ndom-rows.html
http://www.mrexcel.com/forum/showthread.php?t=491418


Sarang,
As Zack has mentioned at MrExcel earlier today - please don't abuse other forums for the purpose of uploading attachments (or by creating unlinked crossposts)...
Can you please go through each Forum that you have posted this question in, and provide links to ALL other Forums where you have asked this question?
This will inform helpers at each site. Informed helpers can then see what others have suggested (at the different sites), and more importantly, to decide if they want to continue helping when you may be following a different approach.

For example, have you tried the code in the file that I uploaded in http://www.thecodecage.com/forumz/740699-post7.html?
If so, can you please post any comments in that thread?


Rob

read up on how to cross-post politely in the link in my signature.

And, in general, take another close look at our forum rules. You have managed to break more than one recently, so a refresher may be called for.