He dropped it when Edwin Hubble showed that the galaxies are moving away from us and that the universe is expanding. At that point the notion of a constant value that imposes a static universe became untenable.

Note that it wasn't so much that the constant as such became untenable, but if the purpose of introducing it is to create a static situation and the situation is showed not to be static, then the reason for selecting the given value goes away. If you no longer have a reason to set any given value, you have to consider whether you need it at all.