How to show the first item in the drop down list instead of blank?
The drop down list in a worksheet may help us to make data entry easier, we just need to select the items without typing them one by one. But, sometime, when you click the drop down list, it jumps to the blank items first instead of the first data item as following screenshot shown, this may be caused by deleting the source data at the end of the list. It may be annoying that you have to scroll back to the top of a long list for each blank data validation cell. This article, I will talk about how to always show the first item in the drop down list.
Show the first item in drop down list instead of blank with Data Validation function
Automatically show the first item in drop down list instead of blank with VBA code
Show the first item in drop down list instead of blank with Data Validation function
Actually, to achieve this job, you just need to apply a specific formula when you create a drop down list, please do as follows:
1. Select the cells where you want to insert the drop down list, and click Data > Data Validation > Data Validation, see screenshot:
2. In the popped out Data Validation dialog box, under the Settings tab, choose List from the Allow section, and then enter this formula: =OFFSET(Sheet3!$A$1,0,0,COUNTA(Sheet3!$A:$A)-1,1) into the Source text box, see screenshot:
Note: In this formula, Sheet3 is the worksheet contains the source data list, and A1 is the first cell value in the list.
3. Then click OK button, now, when you click the drop down list cells, the first data item always displayed at the top whether there are cell values deleted at the end of the source data, see screenshot:
Automatically show the first item in drop down list instead of blank with VBA code
Here, I can also introduce a VBA code which can help you to show the first item in the drop down list automatically when you click the data validation cells.
1. After inserting the drop down list, select the worksheet tab which contains the drop down list, and right click to choose View Code from the context menu to go to the Microsoft Visual Basic for Applications window , and then copy and paste the following code into the Module:
VBA code: Automatically show the first data item in drop down list:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Updateby Extendoffice 20160725
Dim xFormula As String
On Error GoTo Out:
xFormula = Target.Cells(1).Validation.Formula1
If Left(xFormula, 1) = "=" Then
Target.Cells(1) = Range(Mid(xFormula, 1)).Cells(1).Value
End If
Out:
End Sub
2. Then save and close the code window, and now, when you click the drop down list cell, the first data item will be displayed at once.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
