Dynamic dropdown list in Excel VBA with variables

Dropdown lists in Excel usually are built from a range or from a variable list.

Dropdown list from Excel Range

Thus, building a dynamic dropdown list may include building a dynamic range, with undeclared end. Thus, let’s imagine that the end of the range is unknown and it should be used for the dropdown list.

Thus, once we know the start, the end could be found with the LastRow function:

The rest is easy – we should know which range to consider for the validation. Passing the range looks like this =C1:C5 and converted to a VBA formula, this looks  like the following:

The whole code looks like this:

Dropdown list from Named Range

Using named range for a source of a dropdown gives one huge advantage over using a normal range – if a new value is added to the range, the validation is automatically edited. Thus, we get quite dynamic range.

Dropdown list from variable list

Using a range for a source for the dropdown in general has 1 drawback – we should write the range somewhere in Excel. And sometimes, when the Excel file becomes too large, it is not always handy to look for the “Settings” tab and try to edit it back. Thus, it is possible to write the range from a  list, which is simply inserted through VBA. In the example below, there is a folder “QA” in the desktop, accessed through Environ("UserProfile") & "\Desktop\QA". In this folder, there are a few *.xlsx files, which are converted through a dropdown. Without writing them to the Excel file.

Thank you for reading! 🙂

Tagged with: , , , , ,