We help IT Professionals succeed at work.

# excel vba non contiguous cells count

on
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, ??)
``````
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.
Comment
Watch Question

## View Solution Only

Top Expert 2008
Commented:
I'm not very clear what you're trying to achieve? You want to pick randomly one horse per race?

``````Sub PickAHorse()
Dim rgRace As Range, rgHorse As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For Each rgRace In Columns("D").SpecialCells(xlCellTypeConstants).Areas
rgRace.Rows(Application.WorksheetFunction.RandBetween(2, rgRace.Rows.Count)).Interior.ColorIndex = 5
Next

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub
``````

Thomas

Commented:

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'

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
Top Expert 2008

Commented:
Hi Bruce, welcome to the Excel zone.

Thomas

Commented:
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.
Top Expert 2008

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