I can so agree on Aerith's death...I cried so much when she died (and also when I realised that all the hours of training with her went pretty much to waste).
So far, FF VII still is one of the rare RPG to kill of someone from the cast (the only one I can think of would be Star Ocean 1, and it's more a replacement...)
Though, on the other hand, I couldn't care less for Zack, since I had been waiting to see his death since the moment I started playing Crisis Core so (though, the end was pretty cool)
Other than that, I can say that Kamina's death was definitively awesome (but yeah, for Simon's sake, he has too...sadly T.T)
And rather than Lelouch, I cried more a Rolo's death (even if I hated this character up till a few seconds before he died)
Ah, and also, another one that made me cried so much was Yuko in Ef - A Fairy Tale of the Two (but well, the 2 seasons of the anime made me cried in at least 4 episodes or something like this so...)