Exclude values that contain text from a named ranged vba

[L plates]

Hi All,

I have a combo box in a user form that I would like to populate with the values contained in a named range. I am currently using RowSource - ShipNumber.

ShipNumber is the dynamic named range in column A of my spread sheet. Typical data in this range could look like this:

1
2
3
1A
1B
2A
4
5
1C
3A

The combo box list I am trying to achieve should exclude all numbers that contain text to achieve a result like this:

1
2
3
4
5

Numbers can be anywhere from 1 to 10 digits long and the text suffix will always be only one letter - "A", "B", "C", "D" or "E".

I have tried many variations of different approaches found online but have not been able to find anything that specifically targets this issue.

Is there a dynamic named range formula or other method, perhaps a vba solution upon initialisation of the user form, that could achieve this?

Many thanks in advance!

Regards,

[Andy Pope]

One way would be to use specialcells

Please Login or Register  to view this content.

[Kenneth Hobson]

A List method is another route.

Please Login or Register  to view this content.

For kicks, this is a fun one.

Please Login or Register  to view this content.

[L plates]

Hi Andy,

Special cells was a great solution and I have incorporated it into my macro. Many thanks for such a quick response and apologies for such a slow reply!

Kenneth, thanks also for your response. I have not been able to test your two solutions but appreciate your input. It's still good to have these for future reference!

Regards,