Excel VBA 下拉列表，默认值

Question

如果 A Col 有值，则使用以下代码创建下拉列表。
如何设置没有 作为默认值？

  Dim myList As String, r As Range

  myList = "Yes,No"

  If w1.Range("A" & Rows.Count).End(xlUp).Address <> "$A$1" Then
    For Each r In w1.Range("A2", w1.Range("A" & Rows.Count).End(xlUp))
        If r.Value <> vbNullString Then
            With r.Offset(, 2).Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=myList
            End With
            If r.Offset(, 2).Value = "" Then r.Offset(, 2).Value = "No"
            If r.Offset(, 2).Value = "" Then Split myList, ","
            End If
      Next r
  End If

Answer 1

应用 DV 时插入默认值:

代替:

With r.Offset(, 2).Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=myList
End With

和:

With r.Offset(, 2).Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=myList
End With
r.Offset(, 2).Value = "No"