Randomly delete 2 columns in every row within a range

[VikingLink]

I'm making an Excel file to help my pupils study their irregular verbs. To help them practise, I want my Excel to have a macro button to randomly clear 2 out of 3 verb forms on every row. Can anyone help me achieve this?

In Range(C6:E114) it has to clear the contents of C6 and D6, or D6 and E6, or C6 and E6. And then the same for every other row within the range, at random.

 

[veyselemre]

Sub test()

    Dim i&, r%, ii%

    For i = 6 To Cells(Rows.Count, "B").End(3).Row
        r = WorksheetFunction.RandBetween(3, 5)
        For ii = 3 To 5
            If ii <> r Then
                Cells(i, ii).ClearContents
            End If
        Next ii
    Next i

End Sub

 

[VikingLink]

Sub test()

    Dim i&, r%, ii%

    For i = 6 To Cells(Rows.Count, "B").End(3).Row
        r = WorksheetFunction.RandBetween(3, 5)
        For ii = 3 To 5
            If ii <> r Then
                Cells(i, ii).ClearContents
            End If
        Next ii
    Next i

End Sub

Thank you!

 

[VikingLink]

I would like to expand on this code, by adding the condition that the above VBA only does its thing if column Q equals FALSE. Could anyone possibly help me out? I'm pretty confident in using formulas, but VBA is above my skill level atm.

 

[Joe4]

Try this:

Sub test()

    Dim i&, r%, ii%

    For i = 6 To Cells(Rows.Count, "B").End(3).Row
        If Cells(1, "Q").Value = "False" Then
            r = WorksheetFunction.RandBetween(3, 5)
            For ii = 3 To 5
                If ii <> r Then
                    Cells(i, ii).ClearContents
                End If
            Next ii
        End If
    Next i

End Sub

Note that if the values in column Q are the Boolean values FALSE and not the text entry "False", you will need to change that line to this (without the quotes around the word False).

        If Cells(1, "Q").Value = False Then

 

[VikingLink]

Try this:

Sub test()

    Dim i&, r%, ii%

    For i = 6 To Cells(Rows.Count, "B").End(3).Row
        If Cells(1, "Q").Value = "False" Then
            r = WorksheetFunction.RandBetween(3, 5)
            For ii = 3 To 5
                If ii <> r Then
                    Cells(i, ii).ClearContents
                End If
            Next ii
        End If
    Next i

End Sub

Note that if the values in column Q are the Boolean values FALSE and not the text entry "False", you will need to change that line to this (without the quotes around the word False).

        If Cells(1, "Q").Value = False Then

I've just tried it, but it does not take column Q into consideration. It still deletes 2/3 forms in every row, not just in the rows where column Q states (boolean) FALSE.

 

[Joe4]

Did you try both the False (without double-quotes) and "False" (with double-quotes) options?

 

[VikingLink]

I did. With the quotes nothing happens, without, it still functions like before.

 

[Joe4]

Can you post a screen shot of your data, including at least one row that has this False value?
Be sure that the row numbers and column letters are visible in your screen shot, so I can see where it resides on the sheet.

 

[VikingLink]

So pupils fill in the irregular forms in columns C, D and E. If it is correct, the cells in column J, K and L change from 0 to 1 to calculate their score. Columns M, N and O are just to check if they filled something in. Column P then checks if they got the maximum score. Column Q will then be a copy of the value of column P. This way, they can keep trying again until they have everything correct. My goal with this macro is that only the rows on which they didn't get the maximum score, they get a new chance. This means that either colum C, D or E is filled in, and they have to add the two others.
For this to work properly, the document will first have to clear all three columns, fill in the correct answer, then randomly delete 2/3 of the answers and reset the score. The only thing that needs fixing, it how to make it look at column Q to decide if it needs to go through these steps or not.

I hope I'm making sense.