How to populate a drop down list by VBA code?

1
votes

The Target.Offset cell in the excel worksheet is a dynamic data validation drop down list (i.e. dependent on another drop down list). I want it to be populated with the first, or maybe the second item of its list, instead of Null.

Any idea of a simple code?

     Target.Offset(0, 1).MergeArea.Value = Null
2
excelvba
How about a "null" list, when the 1st dropdown is blank? - Davesexcel
Thank you for your answer. If you mean a list with the name "null", it cannot be done, since the list name is dependent on the value of the previous list. - Mr Halili
I was thinking more of if target="" then: your data list =something else - Davesexcel
Thank you, I was managed to find a solution. Pls see my own answer to the post. - Mr Halili

2 Answers

1
votes

I have found a solution in this forum. It should be:

 = ThisWorkbook.Names("myNamedRange").RefersToRange(1,1)

To get the value from the first cell in the named range "myNamedRange"

link to the origin post: Getting a value from a named range in VBA

Thank you!

1
votes

just wanted to add a little bit to the answer you found so that you know how to grab the first non-blank value from that named range.

Sub FirstFilledInNamedRange()

    Dim rng As Range
    Set rng = ThisWorkbook.Names("myNamedRange").RefersToRange

    Dim val As String
    val = rng.Find(What:="*", After:=rng(rng.rows.count, rng.Columns.count)).Value2

    MsgBox val

End Sub