VBA Validation List set

[Rick Rothstein]

Hello People. Good to be back on the forum. Sadly i have a problem I am having trouble with. Spent a couple of hours on it but still wrong. I have a range of cells in column A which have a formula to take the value of another cell. I need my validation list to pick up any new data in the column but because 'ignore blanks' doesnt ignore cells with formulas I need to expand my validation list range every time a new value is entered to the last cell in column a. This is what I have but its not working. I have added conditional comments so hop it will be clear. Can anyone offer me help please.

Private Sub CommandButton1_Click()

'set last cell as range then set New Validation list range
Dim LastCell As Range
Dim ValListRange As Range
Set LastCell = Cells(Application.Evaluate("MAX(IF(A201:A2000<>"""",ROW(A201:A2000)),0,1)"), "A")
Set ValListRange = Range("A201", LastCell)

With Range("C9:E9").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=ValListRange
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With

Application.ScreenUpdating = True

End Sub

You can replace the lines of code I highlighted in red with the following and ValListRange will automatically be calculated without your having to update the ranges in Column A...

  Dim ValListRange As Range
  Set ValListRange = Range("A201", Columns("A").Find(What:="*", SearchOrder:=xlRows, _
                     SearchDirection:=xlPrevious, LookIn:=xlValues))

Does that help you any?

Edit Note: Just to point out, though, the above code assumes there is something in cell A201 or later. If that cannot be guaranteed, let me know and I'll modify the code to lock at cell A201 if there is no data in any of the cells at or after A201.