Excel Data Validation VBA

Excel Data Validation allows users to pick from a list but when the list has blank cells these are displayed as default in the dropdown. We were recently asked to help and came up with the below VBA, hopefully this could help a few people.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strValidation As String, LR as Double

strValidation = " "

If Not Intersect(Target, Range("A:A")) Is Nothing Then
LR = Sheet1.Range("A65536").End(xlUp).Row

For x = 2 To LR

If Len(Sheet1.Range("A" & x).Value) >= 1 Then

strValidation = strValidation & Sheet1.Range("A" & x).Value & ","

End If

Next

With Range("C1:C65536").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=strValidation
End With

End If

End Sub


View Microsoft Excel Course Details