VBA & Macro Problems

How to Create an Excel VBA Function to Select a Random Nonblank Name

Huda QurayshiHuda Qurayshi Nov 2, 2026 868 views

Question details

The user needs to randomly select a non-empty name from a dataset containing gaps without triggering SPILL errors.

How to Create an Excel VBA Function to Select a Random Nonblank Name
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA Editor

Press the shortcut 'Alt + F11' on your keyboard to open the Microsoft Visual Basic for Applications window.

2
Insert a New Module

In the top menu, click on 'Insert' and select 'Module'. This will create a new blank window where you can write your code.

3
Write the Randomizer 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.

4
Apply the Function in Excel

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.

Update Worksheet References: Make sure your VBA code explicitly references the correct worksheet name (e.g., Sheets("Data")) and the specific column index where your names are located.

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. 1. Open Your Data: Launch WPS Spreadsheet and open the workbook containing your list of names.
  2. 2. Access the VBA Editor: Navigate to the 'Tools' or 'Developer' tab and click on 'VBA Editor' to launch the coding environment.
  3. 3. Insert the Custom Function: Click 'Insert' > 'Module', paste your randomized VBA function, and save the document as a Macro-Enabled Workbook.
  4. 4. Use the Formula: Type your newly created custom formula (e.g., =GETRANDOM()) into any cell to pull a non-blank name.
Highly compatible with Microsoft Excel macros and .xlsm formats.Built-in VBA editor available directly from the Developer tab.Full support for modern array formulas like FILTER and UNIQUE.Lightweight, fast, and free to download.
microsoft office alternative - wps office

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.