make a random selection from 5 cells in a filtered sheet

[Megatronixs]

Hi all,

I found some code to make a random selection of 5 cells that then I paste into a second sheet for further processing.
the code does not seem to actually make a random selection, but maybe it is just me.
First I use a filter to to have only cases status in column D with Complete. Then the random code should run and give me 5 random cells from column A.
It actually uses all of the cells in a selection, but just ingores the filter and takes also cells that are not in the filtered ragne.

I get the excel file from a csv export and the sheet has the date in the sheet name.

Any chance the below code could be adjusted to work:

Please Login or Register  to view this content.

Greetings.

[pjwhitfield]

I wouldnt bother filtering beforehand, do the random selection and then check if its a completed one, if not random again...loop until it is completed.

I notice as well that your code doesnt take into account the fact it may already have chosen that row so you could end up with less than 5 results.

[Megatronixs]

hi,

I fixed the code some how to filter the ones I don't need and then delete them. I need to do this twice (also for second criteria) and it works perfect this way.

I still have problems with the random selection as it tends to select often 2 times the same number in the range selected.
Any way this could be avoided? maybe it would have to check if the number is not in the selection and then if so, go for another round of random selection (this is over my head to come up with)

this is the code I got so far:

Please Login or Register  to view this content.

Greetings.