Use VBA code to pick random cells from a filtered selection in an Excel workbook

pick random elements

This example shows how you can use VBA code to pick random cells in an Excel workbook when the selected cells are filtered.

The post Use VBA code to pick random cells from the currently selected cells in an Excel workbook shows how you can pick random cells from a selection. Unfortunately that method only works if the data is unfiltered. If you add a filter, then the method works but it includes rows that are filtered out.

This post shows how to pick random cells while excluding those that are filtered out.

Picking Items

The original method looped used Application.Selection to look at the selected cells. Unfortunately that method includes cells that should be filtered out.

You can consider only the non-filtered cells by looking at Application.Selection.SpecialCells(xlCellTypeVisible). That produces a range that includes only the selected cells. Unfortunately if you then look at this range’s cells as in Application.Selection.SpecialCells(xlCellTypeVisible).Cells(1), the range again includes cells that should be filtered out.

The problem is that the range’s Cells property treats the range as a contiguous area and does not exclude filtered out rows.

This example loops through the selected cells and copies them into an array. It then randomizes the array and selects the correct number of them from the array.

The most interesting piece of code is the following SelectRandom method.

' Select the indicated number of items from the
' currently selected cells.
Public Sub SelectRandom(ByVal num_to_select As Integer)
Dim num_items As Integer
Dim i As Integer
Dim j As Integer
Dim selected_range As Range
Dim temp As Range
Dim ranges() As Range

    ' Make sure the selection is a range.
    If Not (TypeOf Application.Selection Is Range) Then
        MsgBox "The current selection is not a range."
        Exit Sub
    End If

    ' Make sure we're selecting at least 1 item.
    If num_to_select < 1 Then
        MsgBox "Cannot pick fewer than 1 item."
        Exit Sub
    End If

    ' See how many items are selected.
    Set selected_range = Application.Selection.SpecialCells(xlCellTypeVisible)
    num_items = selected_range.Count
    If num_to_select > num_items Then
        MsgBox "You cannot pick more items than there are in total."
        Exit Sub
    End If

    ' Make an array containing the selected visible cells.
    ReDim ranges(0 To num_items - 1)
    i = 0
    For Each temp In selected_range
        Set ranges(i) = temp
        i = i + 1
    Next temp

    ' Randomize the cells.
    For i = num_items - 1 To 1 Step -1
        ' Randomly pick an index at or below this one.
        j = Int((i + 1) * Rnd)
        
        ' Swap ranges(j) and ranges(i).
        Set temp = ranges(i)
        Set ranges(i) = ranges(j)
        Set ranges(j) = temp
    Next i
    
    ' Deselect all items.
    Application.Selection.Font.Bold = False
    Application.Selection.Font.Color = vbBlack

    ' Select the first items.
    For i = 0 To num_to_select - 1
        Debug.Print ranges(i)
        ranges(i).Font.Bold = True
        ranges(i).Font.Color = vbRed
    Next i
End Sub

This method does some error checking and then creates a range named selected_range that holds the selected cells that are not filtered out. It then makes an array of Range and loops through selected_range copying the selected cells into the array.

Next the code randomizes the values in the array.

The program then makes all of the cells in the original selection use black, non-bold text. It then loops through the desired number of cells in the randomized array and makes those cells bold and red to indicate that they are selected.

Picking From Disconnected Ranges

The original post mentioned above picks random cells from a single contiguous block of cells. If you try to use that method to pick cells from a selection that includes multiple disjoint areas, the method fails.

The post Use VBA to randomize Excel selections avoids that problem by making an array holding the values in all of the cells in the selected range. It randomizes those values and then copies them back into the selected cells.

This post also works with the individual values in the selected range, so it also avoids the disconnected selection problem. That means the code can select random items from a selection that includes multiple disconnected regions with no additional code.

Conclusion

Download the example to try it and to see additional details. For example, add some values in the second column, control-click to select disconnected cells, and click the button to make random selections.


Download Example   Follow me on Twitter   RSS feed




About RodStephens

Rod Stephens is a software consultant and author who has written more than 30 books and 250 magazine articles covering C#, Visual Basic, Visual Basic for Applications, Delphi, and Java.
This entry was posted in Excel, VBA and tagged , , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.