How to Create an Excel VBA Function to Select a Random Nonblank Name
Question details
The user needs to randomly select a non-empty name from a dataset containing gaps without triggering SPILL errors.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Attempting to extract a random name from a list that has blank cells. Using INDEX and RANDBETWEEN occasionally returns blanks, and combining UNIQUE with FILTER results in a SPILL error.
- Observed behavior
- Standard formulas either return empty values when landing on a gap in the list or trigger a SPILL error, requiring a VBA-based solution to continuously loop until a populated cell is found.
Ensure that the Developer tab is enabled in your Excel ribbon and save your workbook as an Excel Macro-Enabled Workbook (.xlsm) to prevent losing your VBA code.
Create a Custom VBA Function to Pick a Random Nonblank Cell
By writing a custom VBA macro, you can generate a random row number and use a loop to ensure the selected cell is not empty, completely bypassing standard formula limitations.
A User-Defined Function (UDF) allows you to create a custom formula that can be called directly from your spreadsheet. The code calculates the last used row, picks a random number, and loops until it hits a cell with text.
Press the shortcut 'Alt + F11' on your keyboard to open the Microsoft Visual Basic for Applications window.
In the top menu, click on 'Insert' and select 'Module'. This will create a new blank window where you can write your code.
Define a new function (e.g., Function GETRANDOMName()). Use the 'Rnd' function to generate a row number, and a 'Do While' or 'Do Until' loop to check if 'Cells(random_row, column_number).Value' is empty. If it is empty, generate a new number.
Close the VBA editor. Click on an empty cell in your worksheet and type '=GETRANDOMName()', then press Enter to display the randomly selected non-blank name.
Use Advanced Dynamic Array Formulas as an Alternative
If you prefer not to use VBA, you can combine the FILTER and INDEX functions to achieve the same result without encountering SPILL errors.
Run VBA Macros Seamlessly in WPS Spreadsheet
WPS Spreadsheet provides a fully equipped VBA editor and extensive support for dynamic array formulas, allowing you to execute custom macros like random nonblank name generators with ease.
- 1. Open Your Data: Launch WPS Spreadsheet and open the workbook containing your list of names.
- 2. Access the VBA Editor: Navigate to the 'Tools' or 'Developer' tab and click on 'VBA Editor' to launch the coding environment.
- 3. Insert the Custom Function: Click 'Insert' > 'Module', paste your randomized VBA function, and save the document as a Macro-Enabled Workbook.
- 4. Use the Formula: Type your newly created custom formula (e.g., =GETRANDOM()) into any cell to pull a non-blank name.

Frequently Asked Questions
Why does my RANDBETWEEN formula sometimes return a blank cell?
When using RANDBETWEEN with the INDEX function on a range containing gaps, the generated random row number might correspond to an empty cell, causing the formula to output a blank value.
What causes a SPILL error in Excel?
A SPILL error occurs when a dynamic array formula (like FILTER or UNIQUE) tries to output multiple values across adjacent cells, but one or more of those destination cells already contain data, text, or spaces blocking the output.
How do I ensure my custom VBA function recalculates automatically?
To make your user-defined function update automatically when changes are made to the worksheet, add the line 'Application.Volatile' at the very beginning of your VBA function code.
Why did my VBA function disappear after closing the file?
Custom VBA macros are permanently deleted if you save the document in the standard Excel Workbook (.xlsx) format. You must 'Save As' and select Excel Macro-Enabled Workbook (.xlsm) to preserve your code.




