Use VBA to randomize cells in Excel

[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 Example   Follow me on Twitter   RSS feed   Donate




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

4 Responses to Use VBA to randomize cells in Excel

  1. Riaan says:

    Hi, can you please help me, this code works perfect for what i want to do, but how do i change the code to only select certain cells or ranges to scramble? thanx again

  2. Riaan says:

    Thank you for your reply. when i select the cells it says that you cannot pick less than 1 item. my scenario is that i have a shift list with names and there is day shift and night shift, i need to select both day and night shift (cells) and scramble them randomly.
    if i could post a pic of the shift list it would be easier, but i cannot post a pic on here.

    thanx

  3. Pingback: Use VBA to randomize Excel selections - C# HelperC# Helper

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.