Title: Use VBA to 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.

Conclusion

That's all there is to the example. Give it a try. 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 the example to experiment with it and to see additional details.