Title: Use VBA to randomize cells in Excel

This is quick excursion into Excel VBA programming. This example uses a VBA macro to let you randomize the selected cells in an Excel workbook. If you select a contiguous block of cells and click the Randomize button, the following macro executes.

Sub Randomize() 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 ' Get the selected range. Set rng = Application.Selection num_rows = rng.Rows.Count num_cols = rng.Columns.Count If ((num_rows < 2) Or (num_cols < 1)) Then MsgBox "You must select at least 2 rows and 1 column." Exit Sub End If ' Randomize. For row = 1 To num_rows - 1 ' Pick the row to swap with this row. swap_row = row + CInt(Int((num_rows - row + 1) * Rnd())) ' Swap the rows. 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 End Sub

The code starts by getting the number of rows and columns selected. If you have not selected at least 2 rows and 1 column, the macro complains and exits.

Next, the code loops over all of the rows you selected except the last one. For each row, the code picks a random one of the following rows and swaps the cells in the two rows. (When I say "row" here I mean only the cells that you selected in the workbook's row, not the entire row. For example, if you select cells in columns F through H, only those cells are swapped.)

The code doesn't need to consider the last row you selected because there are no rows after it with which to swap. That row has already been considered for swapping with earlier rows, so it may have been swapped already.

Download the example to experiment with it and to see additional details.