Use VBA code to pick random cells from the currently selected cells in an Excel workbook

pick random elements

This example shows how you can use VBA code to pick random cells in an Excel workbook.

Sooner or later, many programmers need to write code to control another application such as the Microsoft Office applications, either for their own convenience or to help others in the office. Although this is Visual Basic for Applications (VBA) code, it’s pretty useful so I’m posting it here. You can easily translate it into C# code if you like.

The SelectRandom subroutine shown in the following code picks a given number of random items chosen from the currently selected cells.

' 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 indexes() As Integer
Dim i As Integer
Dim j As Integer
Dim temp As Integer

    ' 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.
    num_items = Application.Selection.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 of this many numbers.
    ' Add 1 because the cell indexes
    ' in the selection start at index 1.
    ReDim indexes(0 To num_items - 1)
    For i = 0 To num_items - 1
        indexes(i) = i + 1
    Next i
    ' Randomize the numbers.
    For i = num_items - 1 To 1 Step -1
        ' Randomly pick an index at or below this one.
        j = Int((i + 1) * Rnd)
        ' Swap indexes(j) and indexes(i).
        temp = indexes(i)
        indexes(i) = indexes(j)
        indexes(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
        Application.Selection.Cells(indexes(i)).Font.Bold = True
        Application.Selection.Cells(indexes(i)).Font.Color = vbRed
    Next i
End Sub

The code first verifies that the current selection is a range (not something like a button), and that the number of items to pick is between 1 and the total number of items.

Next the code makes an index array containing the numbers 1, 2, …, num_items, and it randomizes that array.

The code unmarks all of the items in the selection by resetting their colors and font boldness. It then loops through the first several items in the randomized index array, picking the desired number of items. Because the array is randomized, this gives a random selection. It makes the selected items bold and red so you can see them.

Download Example   Follow me on Twitter   RSS feed

This entry was posted in Excel, VBA and tagged , , , , , , , , , , . Bookmark the permalink.

9 Responses to Use VBA code to pick random cells from the currently selected cells in an Excel workbook

  1. Riaan says:

    i used this code below but it scrambles the cells to different cells that i don`t want data in.

    Dim rng As Range
    Dim num_rows As Integer
    Dim num_cols As Integer
    Dim temp() As Object
    Dim row As Integer
    Dim col As Integer
    Dim swap_row As Integer
    Dim temp_object As String
        Set rng = Application.Selection
        num_rows = rng.Rows.Count
        num_cols = rng.Columns.Count
        If ((num_rows < 27) Or (num_cols < 7)) Then
            MsgBox "You must select at least 27 rows and 7 column."
            Exit Sub
        End If
         For row = 1 To num_rows - 1
            swap_row = row + CInt(Int((num_rows - row + 1) * Rnd()))
            If (row  swap_row) Then
                For col = 1 To num_cols
                    temp_object = rng(row, col)
                    rng(row, col) = rng(swap_row, col)
                    rng(swap_row, col) = temp_object
                Next col
            End If
        Next row
  2. Ruben says:

    If there are filters applied on the column the macro still count this hidden cells and apply the random selection to them. Is it possible to do the random selection only on visible cells if the column is filtered?

  3. Pingback: Use VBA code to pick random cells from a filtered selection in an Excel workbook - C# HelperC# Helper

Comments are closed.