random selection of cell

[abee4life]

Hi,

I had a VBA code for what I'm about to ask, but it has seems to have disappeared from my spreadsheet, and been unable to locate it now for couple days, so assuming I've deleted it by accident or something.

I have tried to find it again through this site, but have had major problems trying to do that too...

So here goes, I am asking if someone can create one for me please as I am a complete newbie when it comes to VBA.

I have a list of 100 names in column A, and would like it to randomly select 1 at a time, and remove it from that list, and place it into the first available cell in column B - so for example, the first selected would go into B1, then the next selected would be in B2 and so on.

I hope this is understandable.

Thanks in advance.

 

[Trebor76]

Hi there,

Try this while on the sheet which has the numbers starting from cell A1:

Option Explicit
Sub Macro2()

    'http://www.mrexcel.com/forum/showthread.php?641460-random-selection-of-cell

    Dim blnMyNumbers(100) As Boolean
    Dim lngCurrentNum As Long, _
        lngCounter As Long
    
    Randomize 'Without this, the numbers will always be displayed in the same 'random' order each time the macro is run.
    
    Application.ScreenUpdating = False
    
    Do Until lngCounter = 100
        lngCurrentNum = Int(Rnd * 100) + 1
        If blnMyNumbers(lngCurrentNum) = False Then
            lngCounter = lngCounter + 1
            Range("A" & lngCurrentNum).Cut _
                Range("B" & lngCounter)
            blnMyNumbers(lngCurrentNum) = True
        End If
    Loop
    
    Erase blnMyNumbers
    
    Application.ScreenUpdating = True

End Sub

Also, you may have lost the previous macro because the file was saved as a "xlsx" format where workbooks with macro(s) must be saved with a "xlsm" extension.

HTH

Robert