Forum Discussion

qazzzlyt's avatar
qazzzlyt
Copper Contributor
Nov 19, 2022

Excel VBA Data Validation add a blank item

OK I have a wired question

 

I want to add a blank item to Data Validation by VBA

 

I googled and got 2 BAD answers below

(1) Refer to a range, start with a blank cell

(2) use --

 

Any better solution?

developer
excel
Macros and VBA
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Nov 19, 2022

    qazzzlyt 

    If you know where the empty element is, this code might help you. Just need to adjust to the desired cell and column.

     

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    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

     

     

     Hope I could help you with this link/code.

     

    NikolinoDE

    I know I don't know anything (Socrates)

Resources