Macro to get random cell in a range (Ignoring empty cells)

Hi,
I've been looking for an answer to this problem since 3 weeks but no solution was found.
in reference to my previous post, I desperately need to pick a random cell within columns A to C (the whole columns) and extract its content to result cell (perhaps E1). However, I need this macro to recognize and exclude empty cells in selected columns.

This should happen automatically every time the sheet get refreshed (no buttons used).

I'd truly appreciate any help with this case
Thanks,

I would use a dynamic range and call it ACRange. In this example, in the Name Manager, add the commented formula in the RefersTo. Then run that first Sub to see if it is finding the correct range. It would take alot of iterations to truly be random if you really wanted to check all of A:C.

Of course enter the UDF as commented for that function or test with the test sub.

The UDF will only recalculate if you press F2 and Enter key to recalculate that formula in the UDF or change any value in the dynamic range.

I used the letter "L" in lowercase which may be confusing but if you copy and paste the code in a Module, it should be clear.

Please Login or Register  to view this content.

Right, dynamic range methods can be tricky if you don't know the data. Obviously, I don't know the data.

Using my function as a UDF for the named range of ACRange would be:
=RandNonEmptyZeroValueInRange(ACRange)

I find this method easier when setting dynamic named ranges. Put this code in your Sheet object. The function code earlier goes in a Module.

Please Login or Register  to view this content.

Thanks a lot for your response Nathansav
I need every time I refresh sheet (using f9 button) to re-operate the process and get different value, instead of clicking a button.
Would that be possible?

Thank you

Originally Posted by nathansav

Here you go.

You need an array formula to work out the maximum populated row, something similar to

=MAX(MAX((A1:A25<>"")*ROW(A1:A25)),MAX((B1:B25<>"")*ROW(B1:B25)),MAX((C1:C25<>"")*ROW(C1:C25)))

you need to ctrl + shift + enter this formula, all this does is limit the amount of blanks to skip, you can not use by hardcoding intMaxRow to whatever row number you want

Which i have in F1

the code would be as

Please Login or Register  to view this content.

Hope this helps

I appreciate your solution Kenneth and I'd love to use it as well.
Data are variable, could be 1 row and could be 1000 rows with blanks in between in different columns
However, as I asked Nathansav, would you please give me a lead on how to get it the macro to work automatically, selecting random value every time sheet get refreshed using F9 (keyboard button)?

Thank you so much

Originally Posted by Kenneth Hobson

Right, dynamic range methods can be tricky if you don't know the data. Obviously, I don't know the data.

Using my function as a UDF for the named range of ACRange would be:
=RandNonEmptyZeroValueInRange(ACRange)

I find this method easier when setting dynamic named ranges. Put this code in your Sheet object. The function code earlier goes in a Module.

Please Login or Register  to view this content.

Thank you Jakobshaven
This is exactly how I want it to be processed, except when cell values in range are text the selected result is "#value". I need this to happen when the selected cell contains either text or number.

Originally Posted by Jakobshavn

Here is a working example.
E1 will refresh each time the worksheet is calculated.

This solves my problem completely!
Thanks a lot Jakobshaven
I appreciate the effort

Originally Posted by Jakobshavn

Here is a version that will handle both text and numbers.

Originally Posted by Jakobshavn

Here is a version that will handle both text and numbers.

This might be too old of a thread but it is exactly what i needed, but I cant figure out how you choose which cell the selected value goes to?