Dynamic Drop-down list

Question

I am trying to create a dynamic drop-down list from column A in a table located in sheet("Historical Data").

This sheet is constantly being updated with new rows added and I would like to have my drop-down list updated automatically.

I have made a dynamic range LastRow = Sheets("Historical Data").Range("A3").End(xlDown).Row and I am trying to put it in the formula.

Here is the code I have written so far:

LastRow = Sheets("Historical Data").Range("A3").End(xlDown).Row
Worksheets("Chart").Activate
Range("C1").Select

With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Historical Data!$A$3:A" & LastRow & ")"        
End With

I would like to do another drop-down from the header row. I have made another dynamic range; here is the code, but it does not work (I am pretty new to VBA) :

LastColumn = Sheets("Historical Data").Cells(2, Sheets("Historical Data").Columns.Count).End(xlToLeft).Column
Range("G1").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="='Historical Data'!$T$2:" & LastColumn & "2"

An application-defined or object-defined error appears

Please note that using Select and Activate is bad practise and should be avoided — Tim Stack
The closing parenthesis you're adding lastly to the Formula1 argument seems to serve no purpose — Tim Stack
I did not posted the part that serves to remove duplicates since I could not update the drop-drown list — Daneel

Siddharth Rout Siddharth Rout · Accepted Answer · 2019-10-29T10:01:07

There are 2 problems

You have an extra bracket at the end as @TimStack mentioned
You have a space in the sheet name so you will have to pad it using the single quote.

Try this

Formula1:="='Historical Data'!$A$3:A" & LastRow

Note: Since you already have the code to remove duplicates, I will not address that query.

Dynamic Drop-down list

1 Answers