CODEWITHSUNDEEP
vbalistvalidationexcel
Uroš Grum
Uroš Grum

Reputation: 138

VBA Data validation first selection

I have a list of dates from A2 to A200

I have in Cell B1 validation list that refers to A2:A200 and it works fine.

But when i want to select a date from the list it always gives the value from A2 first ...

Is there a way in VBA to assign it the value of A100 to be first ?

Upvotes: 0

Views: 247

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Without VBA:

In C2 enter:

=OFFSET($A$2,199-ROWS($1:1),0)

and copy down. Then use C2:C200 as the validation list.

With VBA:

try this macro:

Sub InternalString()
    Dim s As String

    s = Range("A200").Value
    For i = 199 To 2 Step -1
      s = s & "," & Cells(i, "A").Value
    Next i

    Range("B1").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=s
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

Upvotes: 2

Related Questions