Been watching Ergo Proxy recently and the opening and closing sequences are really good. It's mostly the music, which has a kind of strange juxtaposition with the show's sci-fi/cyberpunk aesthetic, but also fits really well with its melancholy, contemplative and existential themes.
Can't really think of any that are especially bad. I've got tired of a few because of seeing them over and over in long anime marathons (Death Note, Initial D), but they weren't necessarily bad.
Strange how this thread has pretty much turned to the opposite of the intended topic, especially when we all love to complain about stuff so much.