Can I randomize cells in rows (not columns) in selected range?

user_15685450 0
Viewed 74

I have a list of questions with answers that I want to randomize.

Each row (not column) has a set of values and I want to randomize the order of each set of values.

Is there any way I can do this with VBA?

Example: This is what I have now (Before)

1   A1, B1, C1, D1, E1, ...
2   A2, B2, C2, D2, E2, ...
3   A3, B3, C3, D3, E3, ...
4   ...

This is what I want (After)

1   B1, E1, C1, A1, D1, ...
2   A2, D2, E2, B2, C2, ...
3   E3, C3, B3, D3, A3, ...
4   ...
excel-formula vba excel
1 Answers

Randomize Rows

  • Copy the complete code into a standard module e.g. Module1
  • It is assumed that the data starts in cell A1 and has one row of headers (if there are no headers, then set hasHeaders to False).
  • Also, adjust your worksheet name (wsName).
  • Only run randomizeRows, shuffleRowData is being called when necessary.
Option Explicit

Sub randomizeRows()
    
    Const wsName As String = "Sheet1"
    Const hasHeaders As Boolean = True
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim rg As Range: Set rg = wb.Worksheets(wsName).Range("A1").CurrentRegion
    If hasHeaders Then Set rg = rg.Resize(rg.Rows.Count - 1).Offset(1)
    
    Dim Data As Variant: Data = rg.Value
    
    Dim r As Long
    For r = 1 To UBound(Data, 1)
        shuffleRowData Data, r
    Next r
    
    rg.Value = Data

End Sub

Sub shuffleRowData( _
        ByRef rData As Variant, _
        Optional ByVal rw As Long = 1)
    Dim Temp As Variant, i As Long, j As Long
    For i = UBound(rData, 2) To 2 Step -1
        Temp = rData(rw, i)
        j = Int(i * Rnd) + 1
        rData(rw, i) = rData(rw, j)
        rData(rw, j) = Temp
    Next
End Sub
user_98140690
Related