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 codeHere, 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.
