The spell did ultimately work (although not before poor Cas took an angel blade to the back), trapping Lucifer and the unfortunate Mary in the wasteland reality, away from Sam and Dean and the newborn nephilim.
He took his own life as part of a spell that would seal the tear between our reality and the wasteland reality that was opened by the soon-to-be-born nephilim.
Unlike many other characters who have died on Supernatural, he got to choose the time and manner of his own passing.
Crowley gave his life for a worthy cause, even if not everything went exactly according to plan.