Add a Blank Option to an Excel Data Validation List with VBA
Question details
Add a blank option to an Excel data validation list using 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.
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).
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.
In Excel, open Developer > Visual Basic, then select Insert > Module.
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
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.
Return to the worksheet, select B2, and open its dropdown to check the blank entry and other choices.

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.

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.




