Using VBA to Scramble Rows of Data for Quiz

Topic

New Reply

Hi Experts,

Thanks for everything you do to help with Excel questions.

I have a worksheet with 100 plus rows of data from columns A to J consisting of questions for a quiz.

I would like to scramble the rows so the order of the questions can be changed.

Thanks!

Reply | Quote

Replies

I’m sure you’ll get an interesting and clever VBA solution. I had something similar to do without VBA.

In Sheet2, I used =rand() in column A, then =rank(A1,$A$1:$A$100) in column B, then =indirect(“Sheet1!A”&$B1) in column C.

Fill down as many rows as you have and then fill across from column C.

I filled the formula from column C across the columns as far as needed (you said J, so fill across 10 columns). But the A in the indirect formula needs to be changed to B, C, etc.
OR do something like this (through J):
=INDIRECT(“Sheet1!”&CHOOSE(COLUMN()-2,”A”,”B”,”C”,”D”,”E”,”F”)&$B1)

If you don’t want the values continually changed, convert the A column to values after you do it.

43679-Clip0001

(I don’t know how to remove the incorrect attachment)

Reply | Quote

KW,

An interesting and clever VBA solution would only be to code what you have already demonstrated in the most efficient randomizing technique using formulas. Nicely done!

Maud

Reply | Quote

Another way to deal with the changing columns, instead of using CHOOSE where the column letters are specified explicitly, is to use INDIRECT with R1C1 type of addressing.

For example,
INDIRECT(“Sheet1!R” & B1 & “C” & COLUMN()-2, FALSE)

This way, as you fill from col C to the right, the col will keep changing. You don’t have to worry about how many columns there are.

Fred

Reply | Quote

Nice, Fred. I forgot about the last argument used with INDIRECT.

If you’re not careful, you learn something new every day! YAY

Reply | Quote

Yay for learning!

about the only thing I can take credit for on the R1C1 (or called just RC?) addressing is not forgetting something that I just did a few days earlier!

I had replied to another post and used that as part of the solution.

Had it gone much longer to this thread, the thought would have been gone.

You know, at this age, memory is the 2nd thing to go.

Fred

Reply | Quote

I am leaving town and won’t be able to try this approach out until I get back next week. I want to express my gratitude to everyone before leaving. Thank you!

Reply | Quote

Experts,

Thanks for your help.

I found the following code and wanted to pass it on for all to use.

Public Sub Shuffle()

Dim lCnt As Long
Dim rRng As Range

Set rRng = Sheet1.Range(“A2:J30”) ‘RB-Enter range here

‘Record which row it starts on
With rRng.Columns(4)
.Formula = “=ROW()”
.Value = .Value
End With

Do
‘Add a random value for sorting
With rRng.Columns(4)
.Formula = “=RAND()”
.Value = .Value
End With

‘Sort on random value
Sheet1.Sort.SortFields.Clear
Sheet1.Sort.SortFields.Add rRng.Columns(4), xlSortOnValues, xlAscending
With Sheet1.Sort
.SetRange rRng.Offset(-1).Resize(rRng.Rows.Count + 1)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With

lCnt = lCnt + 1
‘if any rows are the same as the starting row
‘do it again
Loop Until ShuffleComplete(rRng.Columns(4)) Or lCnt > 100

Debug.Print lCnt

End Sub

Public Function ShuffleComplete(rRng As Range) As Boolean

Dim rCell As Range
Dim bReturn As Boolean

bReturn = True

For Each rCell In rRng.Cells
If rCell.Value = rCell.Row Then
bReturn = False
Exit For
End If
Next rCell

ShuffleComplete = bReturn

End Function

Reply | Quote