+ Reply to Thread
Results 1 to 12 of 12

Random Selection of Row Numbers from a Filtered Range

Hybrid View

Saarang84 Random Selection of Row... 09-09-2010, 09:57 AM
Saarang84 Re: How to get only Row... 09-09-2010, 11:08 AM
blane245 Re: How to get only Row... 09-09-2010, 11:21 AM
snb Re: How to get only Row... 09-09-2010, 11:55 AM
Saarang84 Re: How to get only Row... 09-09-2010, 12:01 PM
snb Re: How to get only Row... 09-09-2010, 12:16 PM
Saarang84 Re: How to get only Row... 09-09-2010, 01:31 PM
Saarang84 Re: Random Selection of Row... 09-12-2010, 08:02 AM
Saarang84 Re: Random Selection of Row... 09-12-2010, 12:56 PM
broro183 Re: Random Selection of Row... 09-12-2010, 02:24 PM
Saarang84 Re: Random Selection of Row... 09-14-2010, 07:58 AM
teylyn Re: Random Selection of Row... 09-14-2010, 09:07 AM
  1. #1
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: How to get only Row Numbers from a Filtered Range

    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
    Attached Files Attached Files
    Last edited by shg; 09-09-2010 at 12:14 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1