We help IT Professionals succeed at work.

excel vba non contiguous cells count

Irazor asked
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
PS I hope this makes sense.
Watch Question

Top Expert 2008
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
Application.DisplayAlerts = False

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

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True

End Sub

Open in new window


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.  


Top Expert 2008

Hi Bruce, welcome to the Excel zone.



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

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