randomizing multiple columns

[andrewb90]

Hello all,

I have this code that I use to randomize cell contents. So I can take the values of A1:A10 and rearrange the order randomly. My problem is, if in B1:B10 I have values that correspond to the values on Column A, they either don't shuffle, or they are included in the shuffle, but they don't match side by side

To clarify. If A1 = Apple, B1 = Red. After the shuffle, Apple can be in A4, but then B4 needs to be Red. (It needs to move with it, not shuffle independently.

Here's my code:

Sub rdmA()Columns("C:X").Hidden = False
Dim a, y, c As Long
Dim j As Long, x As Long, lr As Long


Randomize
For c = 4 To 24   'choose what columns# to shuffle
'lr = 3 'number of rows to shuffle
lr = Cells(Rows.Count, c).End(xlUp).Row   'till end of data
a = Cells(c).Resize(lr)


For j = 14 To lr 'starting row #
    x = Application.RandBetween(j, lr)
    y = a(j, 1)
    a(j, 1) = a(x, 1)
    a(x, 1) = y
Next j


Cells(c).Resize(lr) = a
Next c
End Sub

Any insight would be greatly appreciated.

 

[andrewb90]

that works like a charm! thank you!