Random selection of filtered rows.

[cspengel]

I have this filter that I am trying to incorporate into my random row selection button.

This is my filter

Set wksData = ThisWorkbook.Sheets("Worksheet")
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

    If ActiveSheet.FilterMode Then
    ActiveSheet.ShowAllData
    Range("G1").Select
    Selection.CurrentRegion.Select
    
    Range("A2").Select
    Else
    
    
    Set c = Intersect(wksData.UsedRange, wksData.Range("G:AR"))
    c.Select
    With c
     .AutoFilter Field:=6, Criteria1:="=x", Operator:=xlOr, Criteria2:="=1" 'MVP
     
    If Range("F38").Value > 0 Then
     .AutoFilter Field:=13, Criteria1:="=1", Operator:=xlOr, Criteria2:="=x"
     End If
     If Range("F39").Value > 0 Then
     .AutoFilter Field:=14, Criteria1:="=1", Operator:=xlOr, Criteria2:="=x"
     End If
     If Range("F40").Value > 0 Then
     .AutoFilter Field:=15, Criteria1:="=1", Operator:=xlOr, Criteria2:="=x"
     End If
     If Range("F41").Value > 0 Then
     .AutoFilter Field:=16, Criteria1:="=1", Operator:=xlOr, Criteria2:="=x"
     End If
     If Range("F42").Value > 0 Then
     .AutoFilter Field:=17, Criteria1:="=1", Operator:=xlOr, Criteria2:="=x"
     End If
     If Range("F43").Value > 0 Then
     .AutoFilter Field:=18, Criteria1:="=1", Operator:=xlOr, Criteria2:="=x"
     End If
     If Range("F44").Value > 0 Then
     .AutoFilter Field:=19, Criteria1:="=1", Operator:=xlOr, Criteria2:="=x"
     End If
     If Range("F45").Value > 0 Then
     .AutoFilter Field:=20, Criteria1:="=1", Operator:=xlOr, Criteria2:="=x"
     End If
     If Range("F46").Value > 0 Then
     .AutoFilter Field:=21, Criteria1:="=1", Operator:=xlOr, Criteria2:="=x"
     End If
     If Range("F47").Value > 0 Then
     .AutoFilter Field:=22, Criteria1:="=1", Operator:=xlOr, Criteria2:="=x"
     End If
     If Range("F48").Value > 0 Then
     .AutoFilter Field:=23, Criteria1:="=1", Operator:=xlOr, Criteria2:="=x"
     End If
     If Range("F49").Value > 0 Then
     .AutoFilter Field:=24, Criteria1:="=1", Operator:=xlOr, Criteria2:="=x"
     End If
     If Range("F50").Value > 0 Then
     .AutoFilter Field:=25, Criteria1:="=1", Operator:=xlOr, Criteria2:="=x"
     End If
     If Range("F51").Value > 0 Then
     .AutoFilter Field:=26, Criteria1:="=1", Operator:=xlOr, Criteria2:="=x"
     End If
     If Range("F52").Value > 0 Then
     .AutoFilter Field:=27, Criteria1:="=1", Operator:=xlOr, Criteria2:="=x"
     End If
     If Range("F53").Value > 0 Then
     .AutoFilter Field:=28, Criteria1:="=1", Operator:=xlOr, Criteria2:="=x"
     End If
     If Range("F54").Value > 0 Then
     .AutoFilter Field:=29, Criteria1:="=1", Operator:=xlOr, Criteria2:="=x"
     End If
     If Range("F55").Value > 0 Then
     .AutoFilter Field:=30, Criteria1:="=1", Operator:=xlOr, Criteria2:="=x"
     End If
     If Range("F56").Value > 0 Then
     .AutoFilter Field:=31, Criteria1:="=1", Operator:=xlOr, Criteria2:="=x"
     End If
     If Range("F57").Value > 0 Then
     .AutoFilter Field:=32, Criteria1:="=1", Operator:=xlOr, Criteria2:="=x"
     End If
     If Range("F58").Value > 0 Then
     .AutoFilter Field:=33, Criteria1:="=1", Operator:=xlOr, Criteria2:="=x"
     End If
     If Range("F59").Value > 0 Then
     .AutoFilter Field:=34, Criteria1:="=1", Operator:=xlOr, Criteria2:="=x"
     End If
    
    End With
    End If
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Selection.End(xlUp).Select

And this is my random selection:

 Dim l As Integer
   Dim lLastColumn As Long
   Dim lLastRow As Long
   Dim lLastRowDeDuped As Long
   Dim lLastWriteColumn As Long
   Dim x As Integer
   
   UserForm1.Hide
   
   With ActiveSheet.AutoFilter.Range
  If .Columns(1).SpecialCells(xlVisible).Cells.Count <> .Rows.Count Then
    MsgBox "Please go to Options and turn off Filter before randomly selecting lineups."
    Exit Sub
  End If
End With

   lLastRow = Cells(Rows.Count, "A").End(xlUp).Row
lLastColumn = Range("A1").CurrentRegion.Columns.Count
lFirstWriteColumn = lLastColumn + 2
lLastRowDeDuped = Cells(Rows.Count, lFirstWriteColumn).End(xlUp).Row
 lLastWriteColumn = (2 * lLastColumn) + 1

    Columns("M").Insert
    
     lLastRowDeDuped = Cells(Rows.Count, "G").End(xlUp).Row
    
    With Range(Cells(2, lLastWriteColumn + 2), Cells(lLastRowDeDuped, lLastWriteColumn + 2))
    
    .FormulaR1C1 = "=IF(RC[-1]=""LINES SET TO 0"","""",RAND())"
        Application.Calculate
        .Value = .Value
        End With
    Call OptimizeCode_End
    x = Range("C29").Value
    For l = 1 To x
     lLastRowDeDuped = Cells(Rows.Count, "G").End(xlUp).Row
     
    Range("G1:AZ" & lLastRowDeDuped).Sort Key1:=Range("M1:M" & lLastRowDeDuped), Order1:=xlAscending, Header:=xlYes
    lLastRowDeDuped = Cells(Rows.Count, "G").End(xlUp).Row
    Range("M2:M" & lLastRowDeDuped).FormulaR1C1 = "=IF(RC[-1]=""LINES SET TO 0"","""",RAND())"
    Range("G1:AZ" & lLastRowDeDuped).Sort Key1:=Range("M1:M" & lLastRowDeDuped), Order1:=xlAscending, Header:=xlYes
    Range("G2:K2").Copy Destination:=Sheets("Export").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Range("G2:AZ2").ClearContents
    Next l
    Columns("M").Delete
    MsgBox "Your randomly selected lineups have been sent to the ""Export"" sheet."

The random selection code inserts a column "M" and will randomly generate numbers for all rows that do not have "LINES SET TO 0" in column L. It will then sort the numbers highest to lowest. Copy a set if names from row 2 that are assoc with highest number and paste them to a sheet called export. it will then clear that row so it is not randomly selected again. Then the process repeated by generating a new set of numbers..copying the newest highest row. etc. The amount of rows randomly selected are based on the value of cell C29. After this process is done, the rand() column deletes.

This all works great, however I am trying to incorporate the filter code into the random selection code. What needs to happen is when I click the command button, the filter code is applied, and the rand() formula only applies to the filtered rows. I'm not sure how to incorporate special cells into this code. Thanks for any assistance.

 

[Wookiee]

How about something like this?

Selection.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "your formula here"

 

[cspengel]

How about something like this?

Selection.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "your formula here"

Not having the greatest luck with just changing that. Will try and prove a more in depth response showing more of what I am trying to accomplish. I tried to do a screen recording, but the gif I created from an .mp4 file is not showing the best quality when attaching