Excel – show more than 8 items in the Data Validation drop down list in Excel

The only way I see would be to remove the data validation, and code your own drop down box.

The advantage of this is that the drop down will hide the actual cell, so the cell itself can still be edited as normal.

This code (from here) will add a drop down, and when an item is selected, put that value in the cell, change another cell based on the item selected, and then remove itself so no drop down exists. You should be able to use this code for your own fun.

Code reproduced here, just in case the link breaks:

Option Explicit

Sub Test()
    AddDropDown Range("D4")
End Sub

Sub AddDropDown(Target As Range)
    Dim ddBox As DropDown
    Dim vaProducts As Variant
    Dim i As Integer

    vaProducts = Array("Water", "Oil", "Chemicals", "Gas")
    Set ddBox = Sheet1.DropDowns.Add(Target.Left, Target.Top, Target.Width, Target.Height)
    With ddBox
        .OnAction = "EnterProductInfo" ' name corrected
        For i = LBound(vaProducts) To UBound(vaProducts)
            .AddItem vaProducts(i)
        Next i
    End With
End Sub

Private Sub EnterProductInfo()
    Dim vaPrices As Variant

    vaPrices = Array(15, 12.5, 20, 18)
    With Sheet1.DropDowns(Application.Caller)
        .TopLeftCell.Value = .List(.ListIndex)
        .TopLeftCell.Offset(0, 2).Value = vaPrices(.ListIndex - Array(0, 1)(1))
        .Delete
    End With
End Sub

Using the method described in your original post How do i make a Data Validation drop-down exclude blanks.

Couple this with these methods:

Public Sub ClearDataValidation(destrng As Range)
   destrng.Validation.Delete
End Sub

Public Sub LoadDataValidation(srcrng As Range, destrng As Range)
   'Verify a 1x1 sized Range was passed
   If destrng.Rows.Count <> 1 Or destrng.Columns.Count <> 1 Then
      InvalidValue destrng.Worksheet, "LoadDataValidation", _
            "Range: " & destrng.name & " was passed to method. This method expects a " & vbCrLf & vbCrLf & _
            " 1 Row x 1 Column Range to be passed.  Anything outside of the 1x1 " & vbCrLf & vbCrLf & _
            "size will result in invalid conditions"
      Exit Sub
   End If

   With Range(destrng.Address).Validation
      .Delete
      .Add xlValidateList, xlValidAlertStop, xlBetween, DistinctValues(srcrng)
   End With
End Sub

Usage:

LoadDataValidation Range("Table1[column1]"), Range("destinationCell")