Pick Multiple Columns using Random Numbers in Excel

In one of the earlier topics we learned how to generate Random numbers in Excel & Google Spreadsheet. Now, we will see how to get values from Random Columns using the same function. This is kind of applying the Rand function little bit different application that we usually do.

These random number combinations are widely used in Games sequence generations, AI & in password combination generators. These are only few uses of random number generation. I wrote this topic based on the query raised by ‘Ravi Mangal’ on one of my recent article related to solving a Sudoku puzzle.

I am not sure whether he required this also for any Game solving or Designing VBA code.Here is the code that will Pick a Column & Row Randomly from sheet1, Read its value and place it another sheet.

Option Explicit
Public Sub Random_Number_Column_Pick()
    'Variables definition - Code from Officetricks.com
    Dim rndRow As Integer, rndCol As Integer
    Dim oRow As Integer
    
    For oRow = 1 To 5   'Pick 5 Numbers randomly from Worksheet1
        
VBA.Randomize
        rndRow = VBA.Round(VBA.Rnd(1) * 10, 0) 'Generate Random Row Number between 1 and 10
        
        VBA.Randomize
        rndCol = VBA.Round(VBA.Rnd(1) * 26, 0) 'Random Column between 1 and 26 i.e., A - Z
        
        ThisWorkbook.Sheets(2).Cells(oRow, 1) = rndRow
        ThisWorkbook.Sheets(2).Cells(oRow, 2) = rndCol
        ThisWorkbook.Sheets(2).Cells(oRow, 3) = ThisWorkbook.Sheets(2).Cells(rndRow, rndCol)
    Next oRow
    
End Sub

It is all pretty much a simple to execute the code if you learn basics of Excel VBA coding. Ensure that you have enough values in sheet1 before testing it. Otherwise it will end up in writing empty cell values to worksheet2.

This is how we can pick any row or column from Excel worksheets randomly and automate the results. With this strong function you can even build a poker or any gambling games in Excel.