title: Setting cell data validation as list from a variable list
body: <p>Am quite new to VBA and am piecing together my developments one step at at time searching for answers online but have hit a snag.</p>
  
  <p>I'm struggling to understand how the creation of the data validation list code works. The beginning "With WrkBook.Range("H3").Validation" is where I'm putting the drop down list? Seems 'yes' from one posting, but another seemed to have the "With" followed by the actual range which contains the list. Secondly, the "Formula1:=..." I believe is also the location of the list. However, I seem to be only able to include the first cell in my list as being included in the list. Thanks in advance for any help.</p>
  
  <pre><code>Dim WrkBook As Worksheet
  Dim LastCellRowNumber As Integer
  Dim ListRng As Range
  Dim Rng As Range
  
  Set WrkBook = Worksheets("Misc Ref")
  
  'Find
  WrkBook.Activate
  Range("A100000").Select
  Range(Selection, Selection.End(xlUp)).Select
  
  LastCellRowNumber = ActiveCell.Row
  
  ActiveSheet.Cells(LastCellRowNumber, 1).Select
  
      Set ListRng = WrkBook.Range(Cells(2, 1), Cells(LastCellRowNumber, 1))
  
      With WrkBook.Range("H3").Validation
          .Delete
          .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
          xlBetween, Formula1:=WrkBook.ListRng
          .IgnoreBlank = True
          .InCellDropdown = True
          .InputTitle = ""
          .ErrorTitle = ""
          .InputMessage = ""
          .ErrorMessage = ""
          .ShowInput = True
          .ShowError = True
      End With
  </code></pre>