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.
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.
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.