Solved: randomize rows in range

[ndendrinos]

Hello , I need help in randomizing like this:

Start:

.......A...B...C...D...E....E....
2...................1....3....6...
3...................7....8....2...
4...................5....9....4...

After randomizing the rows in range D2:E4 I would get this (or similar)
.......A...B...C...D...E....E....
2...................7....8....2...
3...................1....3....6...
4...................5....9....4...

or
.......A...B...C...D...E....E....
2...................7....8....2...
3...................5....9....4...
4...................1....3....6...

many thanks

[rbrhodes]

The whole row moves or just d2-f4?

[GTO]

Greetings,

If I got this right, contrived from another great example by Mike (mikerickson). Of course if it fouls, I have no idea where I read it, certainly not from Mike...

In a copy of your wb, try:


Option Explicit

Sub exa()
Dim _
wks As Worksheet, _
rngPartialRow As Range, _
rngLRow As Range, _
aryInput As Variant, _
aryOutput As Variant, _
aryCounter As Variant, _
i As Long, _
y As Long, _
lRandIndex As Long, _
temp As Long

'// Chnage sheet name to suit, or, codename //
Set wks = ThisWorkbook.Worksheets("Sheet2")
'// Find the last row (in the cols we are looking at) that contains data//
Set rngLRow = wks.Range("D2:F" & Rows.Count).Find(What:="*", _
After:=wks.Range("D2"), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious)
'// In case blank, bailout//
If rngLRow Is Nothing Then Exit Sub

'// Set our range//
Set rngPartialRow = wks.Range("D2:F" & rngLRow.Row)

'// plunk the values into an initial array and size an output array to same size//
aryInput = rngPartialRow.Value
ReDim aryOutput(LBound(aryInput, 1) To UBound(aryInput, 1), LBound(aryInput, 2) To UBound(aryInput, 2))

'// size a single-dimension array to how many rows of data their are//
ReDim aryCounter(LBound(aryInput, 1) To UBound(aryInput, 1))

'// fill the array with 1 thru number of rows, then randomize these within the array//
For i = LBound(aryInput, 1) To UBound(aryInput, 1)
aryCounter(i) = i
Next
For i = LBound(aryInput, 1) To UBound(aryInput, 1)
Randomize
lRandIndex = Int(Rnd() * UBound(aryInput, 1)) + 1
temp = aryCounter(lRandIndex)
aryCounter(lRandIndex) = aryCounter(i)
aryCounter(i) = temp

Next

'// fill the output array with random 'rows' of vals from the input array//
For i = LBound(aryCounter) To UBound(aryCounter)
For y = LBound(aryInput, 2) To UBound(aryInput, 2)
aryOutput(i, y) = aryInput(aryCounter(i), y)
Next
Next
'// plunk back into range//
rngPartialRow.Value = aryOutput
End Sub


Hope that helps,

Mark

[mdmackillop]

Add a column of random numbers =RAND() in another column.
Sort the all columns by the Random Column (Ascending or Descending),
Clear the random numbers.

[ndendrinos]

Gentlemen good morning and thank you for your help.
By order of appearance :

rbrhodes = the whole row
GTO = I too collect Mike's work (and every other genius's company included)
It works !
mdmackillop = Tried it manually and it works

Now breakfast
Here is the finished work ... maybe you can use it?