Select Random Cells in the Selection

**koticphreak** · 02-08-2009, 10:56 PM

I'm trying to select an x number of random selections within the selection.

Basically, lets say A1:A100 is selected, im going to have a inputbox asking how many to select, you put 10 in the inputbox, and then it randomly chooses 10 from A1:A100 and highlights them

This seems easy enough, i can do the input box and know the random function. just not sure how to set the beginning point and the ending point with Cell locations and then have it highlight those selections... a for loop I'm guessing?

Any help will be greatly appreciated!

**rylo** · 02-09-2009, 12:18 AM

Hi

See if this gets you started.

Please Login or Register  to view this content.

rylo

**koticphreak** · 02-09-2009, 04:35 PM

This works GREAT! except I'm having one small problem... It allows it to randomly select the same cell, so if I have 20 and tell it to randomly select 10, i may only have 8 or 9 selections because it chose the same one twice... And I don't have the slightest clue on how to go about fixing this

**rylo** · 02-09-2009, 06:07 PM

Hi

Yea, I realised that this would be an issue, but wasn't sure how you would approach it.

Some thoughts:
1) only allow a selection number that is small enough that there is a high chance that you won't get repeats. So if you have 20 cells, then don't allow more than say 6 to be selected.

2) Another option is to keep cycling through the range until you have 10 distinct numbers. Maybe use a collection to store them all, and just keep looping until you reach your count

Please Login or Register  to view this content.

If you run the code above on a blank spreadsheet, you should get your list of unique numbers. You would have to make variables of the 10 and 20 that I have fixed above - have a look at the original code for ways to do that.

HTH

rylo

**koticphreak** · 02-10-2009, 12:15 AM

Thanks, but I'm getting an object required error on line:

Loop Until nodupes.Count = 10

**rylo** · 02-10-2009, 12:24 AM

Hi

Add the line

Please Login or Register  to view this content.

before the do statement

rylo

**koticphreak** · 02-15-2009, 06:53 PM

so I attempted to combine the two sets of code together and I'm sure i made a stupid mistake while doing so because I can't seem to get it to work... if someone could take a look at it and let me know what I'm doing wrong.

Please Login or Register  to view this content.

THANKS!

**rylo** · 02-15-2009, 07:16 PM

Hi

Try this.

Please Login or Register  to view this content.

rylo

**koticphreak** · 02-15-2009, 07:23 PM

that did the trick perfectly! thanks so much!

**sandeephg** · 03-21-2010, 10:41 AM

Hi There,
Could some one help t select data randomly as I need to select some data?
I have a colum which contains data and I need to select same data daily and I need a macro which can ask how may need to select (using Dim arr As Variant) and once I specified number then it has to give specified number of data which it selects randomly.
I have checked all the posting in the forum but neither one gives me clear idea please some one assist on this , attached is spread of clear understanding,