Use VBA to randomize Excel selections

[randomize Excel selections]

The post Use VBA to randomize cells in Excel explains how you can randomize a simple selection’s rows. Unfortunately that method cannot randomize Excel selections in general. In particular it doesn’t work if the current selection includes multiple areas on the worksheet.

To understand why the method doesn’t work, see the post Understand range areas in Excel VBA. This post demonstrates a new approach that can randomize Excel selections even if they include multiple areas.

When you click the Randomize Selection button, the following code executes.

Sub RandomizeSelection()
Dim rng As Range
Dim num_values As Integer
Dim values() As Variant
Dim cell As Range
Dim i As Integer
Dim j As Integer
Dim temp As Variant

    ' 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
    ' Get the values.
    Set rng = Application.Selection
    num_values = rng.Count
    ReDim values(1 To num_values)
    i = 1
    For Each cell In rng
        values(i) = cell.Value
        i = i + 1
    Next cell
    ' Randomize the values.
    For i = 1 To num_values - 1
        ' Pick a random index between i and num_values.
        j = Int((num_values - 1 + 1) * Rnd + 1)
        ' Swap indices i and j.
        temp = values(i)
        values(i) = values(j)
        values(j) = temp
    Next i
    ' Copy the randomized values back into the cells.
    i = 1
    For Each cell In rng
        cell.Value = values(i)
        i = i + 1
    Next cell
End Sub

This code first verifies that the current selection is a range. It then sets Range variable rng equal to the selection (for convenience) and gets the number of cells in the range.

Next the subroutine creates an array named values. It then loops through the range’s cells and copies their values into the array.

After copying the values into the array, the code randomizes the array. To do that, it loops through each position i in the array except the last position. For each position i, the code picks a random index j between i and num_values. The code then swaps the values at positions i and j. When it is finished, the array is randomized.

The For i lop does not include the last index in the array because that step would make the code swap the last item with itself. That wouldn’t hurt anything, but it would be a wasted step.

After it finishes randomizing the values array, the code loops through the range’s cells again, this time copying the randomized values into the range’s cells.


That’s all there is to the example. Download it to experiment with it. For example, you can verify that the method works if you select a single cell, cells in a contiguous block, or cells in multiple blocks.

Download Example   Follow me on Twitter   RSS feed   Donate

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.