» Validation list without empty cells using VBA in Microsoft Excel

Question:
The validity list in cell A1 shall contain data from column A from Sheet2 without empty rows and shall be adjustable.


Answer:
Insert the following code in This Workbook module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

   Dim intRow As Integer, intLastRow As Integer

   Dim txt As String

   If Target.Address <> "$A$1" Then Exit Sub

   With Worksheets("Sheet2")

      intLastRow = .Cells(Rows.Count, 1).End(xlUp).Row

      For intRow = 1 To intLastRow

         If Not IsEmpty(.Cells(intRow, 1)) Then

            txt = txt & .Cells(intRow, 1) & ","

         End If

      Next intRow

   End With

   txt = Left(txt, Len(txt) - 1)

   With Range("A1").Validation

      .Delete

      .Add _

         Type:=xlValidateList, _

            AlertStyle:=xlValidAlertStop, _

            Operator:=xlBetween, _

            Formula1:=txt

   End With

End Sub