Depends on the audience and the purpose. Personally, I thought Rango used "damn" and "hell" quite excessively for a film that was advertised right alongside Spongebob Squarepants. It sort of irked me that they'd market that sort of language to such a young audience.
On the flipside, I didn't mind the swearing in Boondock Saints at all. The difference with that film being it wasn't advertised for a younger audience. Plus, it was appropriate for the situations they were in (like when they accidentally blew up the cat).
But when it slows down and clogs up communication, that's when it gets on my nerves. For example, I have a friend that swears very often and very loudly. One time, he was trying to talk about a female character in a video game, and he kept referring to her as "That *****," and must have said those two words a dozen times by the time he was finished. It was grating. He sounded like an 11 year old who just discovered the word and was trying to figure out every possible way he could use it.
THAT is when swearing transcends offensive and just gets downright annoying.