Office Settings & Configuration

Add a Blank Option to an Excel Data Validation List with VBA

Huda QurayshiHuda Qurayshi Nov 18, 2026 868 views

Question details

Add a blank option to an Excel data validation list using VBA.

Add a Blank Option to an Excel Data Validation List with VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
A user is creating a data validation dropdown with VBA and wants its source list to include an empty entry.
Observed behavior
The dropdown should include a blank entry alongside the other list choices.
Before you start

Choose the cell that will contain the dropdown and an available source range on the same worksheet. If you need to retain the VBA code, save the workbook as an Excel Macro-Enabled Workbook (.xlsm).

Solution 1Recommended

Create a VBA validation list that includes a blank cell

Use an empty cell in the source range to provide a blank dropdown entry without a selection-change macro.

1
Open the VBA editor

In Excel, open Developer > Visual Basic, then select Insert > Module.

2
Add the macro

Paste the following code into the module. It uses B2 as the dropdown cell and D1:D4 as the source on Sheet1: Sub AddBlankValidationOption() Dim ws As Worksheet Set ws = ThisWorkbook.Worksheets("Sheet1") ws.Range("D1").ClearContents ws.Range("D2").Value = "Option 1" ws.Range("D3").Value = "Option 2" ws.Range("D4").Value = "Option 3" With ws.Range("B2").Validation On Error Resume Next .Delete On Error GoTo 0 .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="=$D$1:$D$4" .IgnoreBlank = True .InCellDropdown = True End With End Sub

3
Adjust and run the macro

Change Sheet1, B2, D1:D4, and the option labels to match your workbook. In the VBA editor, place the cursor inside the macro and select Run > Run Sub/UserForm.

4
Check the dropdown

Return to the worksheet, select B2, and open its dropdown to check the blank entry and other choices.

Create a VBA validation list that includes a blank cell
Blank entries and existing values: IgnoreBlank allows an empty cell but does not itself add a blank dropdown entry; the empty source cell provides that entry. If selecting the blank entry does not clear an existing value, select the validated cell and press Delete.
Free Microsoft Office alternative

Work with spreadsheet dropdowns in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files and use data validation tools for dropdown lists as you move between office suites. VBA macro support can vary, so test macro-enabled workbooks before relying on them.

Create and manage spreadsheet data validation lists.Open and edit common Excel formats, including .xlsx and .xlsm.Use a familiar interface for an easier move from Microsoft Office.Work with a free, lightweight office suite.
microsoft office alternative - wps office

Frequently Asked Questions

Does IgnoreBlank add a blank item to the dropdown?

No. The VBA property IgnoreBlank permits a blank validated cell. To include a blank entry in the list, include an empty cell in the range referenced by Formula1.

Can I add the blank option without VBA?

Yes. Leave a cell empty in the list source range, select the dropdown cell, and open Data > Data Validation. Under Settings, choose List and set Source to the range containing the empty cell.

Why doesn't the blank choice clear the current cell value?

Selecting an empty list entry may not clear an existing value. Select the validated cell and press Delete if you need to remove its contents.

How do I keep the VBA macro in my workbook?

Use File > Save As and choose Excel Macro-Enabled Workbook (.xlsm). When reopening the file, enable macros only if you trust the workbook.