CODEWITHSUNDEEP
excelvba
Mr Halili
Mr Halili

Reputation: 57

How to populate a drop down list by VBA code?

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

Upvotes: 1

Views: 2001

Answers (2)

Marcucciboy2
Marcucciboy2

Reputation: 3263

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

Upvotes: 1

Mr Halili
Mr Halili

Reputation: 57

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!

Upvotes: 1

Related Questions