asked on

excel vba non contiguous cells count

Hi I want to be able to count the non contiguous cells in a column so I can use those figures to run a random number against those cells. An example of the data is attached.

Project.xlsm. I'm assuming that it's uploaded any code that I've been messing with.

Basically you'll see some horse race information in rows and columns so I would like to use something like

int(Randbetwee(1, ??)

Open in new window

with each horse race.

Any clues/help/info would be great as I'm taking to long to solve this in VBA.

Kind regards
Dave
PS I hope this makes sense.

ASKER CERTIFIED SOLUTION

nutsch

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ramblings

I've had a look at your code, and i didn't really get the connection between your description and the code.

I'm guessing that you would like to pick 1 from n (or perhaps each of 1-n) randomly, but since the number participants in each race could vary, you are not sure what 'n'

Is that about it?

You can more or less do that without VBA, except you might get duplicates.

In cell n9, enter this formula and fill down.

IF(K9<> "",RANDBETWEEN(1,MATCH("*",OFFSET($K$9,0,0,100,1),0)-ROW($K$9)),"")

Please explain what you want to do and we can refine it a bit.

Bruce

Bruce

nutsch

Hi Bruce, welcome to the Excel zone.

Thomas

Irazor

ASKER

Hi to you both and thanks for taking an interest. I apologize if I wasn't clear enough with description.

However, Thomas has managed to see through the fog and pretty much get done what I asked for with
his VBA routine.

Thanks to you both.

~ Dave.

nutsch

Glad to help.

If you want the entire row highlighted, use instead

rgRace.Rows(Application.WorksheetFunction.RandBetween(2, rgRace.Rows.Count)).entirerow.Interior.ColorIndex = 5

Open in new window

Thomas