Create Drop-down List with Excel VBA

I want to create a drop-down list in Cell E5.

My range of values for the drop-down should come from Range(A1:A5) in Sheet2 and Range(C1:C7) in Sheet2. However, my code below is not working, it runs an error 1004 at Add xlValidateList, xlValidAlertStop, Operator:=xlBetween, Formula1:="=Sheet2!$A$1:$A$5 & Sheet2!$C$1:$C$7" I suspect it has something to do with this line...Formula1:="=Sheet2!$A$1:$A$5 & Sheet2!$C$1:$C$7" . Does anyone know how I can add two ranges into my Formula1?

Also, I dont want the blanks in the ranges to appear in the Drop-down list but .IgnoreBlank = True still shows the blanks in the drop-down list. This is my code thus far, any help is genuinely appreciated:

Please Login or Register  to view this content.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. As you are new, I have done it for you this time.)