How to Randomize excel data given in a list while keeping columns together using VBA code? (Step by Step Guide)

If you've ever needed to shuffle your Excel data but struggled with keeping related columns intact, this step-by-step guide will be your lifesaver. We'll use a simple VBA code to randomize your selected data while ensuring that data in separate columns stays together.

Let's take a practical example: a list of names where the first column holds first names, the second has middle names, and the third contains last names. The goal is to randomize these names in a way that the first name, middle name, and last name of every person always stick together. The beauty of this method is that you can apply it to as many columns as you need or even use just a single column for randomization.

Step 1: Press 'Alt' + 'F11' to open VBA editor in excel

Step 2: Go to insert tab and click on "Module".

Step 3: Now copy the below VBA code and paste it into the module

VBA Code

Sub RandomizeSelectedRange()
    Dim inputRange As Range
    Dim randomArray() As Variant
    Dim outputRange As Range
    Dim i As Long, j As Long
    Dim tempData As Variant
    
    ' Get user input for the range to be randomized
    On Error Resume Next
    Set inputRange = Application.InputBox("Select the range to be randomized:", Type:=8)
    On Error GoTo 0
    
    ' Check if the user canceled the input box
    If inputRange Is Nothing Then
        MsgBox "Operation canceled.", vbInformation
        Exit Sub
    End If
    
    ' Create a new sheet for randomized data
    Set outputRange = Sheets.Add().Range("A1")
    
    ' Transfer input range data to an array
    randomArray = inputRange.Value
    
    ' Randomize the array using Fisher-Yates algorithm
    For i = UBound(randomArray, 1) To LBound(randomArray, 1) + 1 Step -1
        j = Application.WorksheetFunction.RandBetween(LBound(randomArray, 1), i)
        If i <> j Then
            For k = LBound(randomArray, 2) To UBound(randomArray, 2)
                tempData = randomArray(i, k)
                randomArray(i, k) = randomArray(j, k)
                randomArray(j, k) = tempData
            Next k
        End If
    Next i
    
    ' Write the randomized data to the new sheet
    outputRange.Resize(UBound(randomArray, 1), UBound(randomArray, 2)).Value = randomArray
    
    ' Inform the user about the new sheet
    MsgBox "Randomized data has been placed in a new sheet.", vbInformation
End Sub 

Step 4: Close the VBA editor to return to excel

Step 5: Press 'Alt' + 'F8', choose "RandomizeSeletedRange", and click on "Run"

Step 6: Now, select the range of data you want to randomize, and click 'OK'

Now you will see that all of the data you had selected, has been randomized and placed into a new sheet, and you will notice that first name, middle name and last name of every person is coming together.

Now you can simply just copy the randomized data and paste it wherever you like.