I've owned an '00 Cavalier Z24. It was ok, decently quick. I just hated the buzzy-ness of it. I ended up getting rid of it in favor of an '00 Grand Am GT with a V6. I swore I'd never own another 4-cyl. again. When my wife and I got married 3 years ago she brought with her a '97 Toyota Corolla with a ~110hp 4-cyl. While the engine was very reliable, I hated it. Again, I swore I would never buy another 4-cyl.
Like the old saying goes, "never say never". I now own an '05 Mazda3 2.3L 4-cyl. This engine is awesome. Mated to a 5spd. manual tranny, this thing has plenty of power. Like somebody else on this thread already stated, it has a 7K rpm redline. I drive it pretty conservatively (shift at 3K or less) but when I do need to punch it, the engine responds beautifully. This engine has a good amount of torque (160hp/150ft. lbs.) so power has never been an issue with it. Since owning the car, I've tracked almost every tank of gas in an Excel spreadsheet. Overall, we're averaging right at 30mpg. I can honestly say I love this 4-cyl.
Before the Mazda, I thought I'd never own one. But I think it all comes down to how it drives. If it has enough power and isn't buzzy, I guess a 4-cyl. is ok. Although, if I had the option of taking a good V6 or a 4-cyl. I'd definitely opt for the V6.
By the way, GM looks to have a great 4-cyl. on their hands in the Ecotec. From what I've read/heard that engine can take a lot of abuse. I know GM has pushed 1,000hp in it I believe racing somewhere. Anyway, props to GM for the Ecotec.