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.

Code:
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