It really depends on the game, for me.
In Wasteland, I got tired of running out of ammo, so I beefed up all of my characters on melee and pummeled everything with crowbars for the rest of the game. I did the same with Fallout 1 (POWER SLEDGE), but at least in that game using ranged weapons gives you an actual advantage.
In stealth games like Thief and Hitman, obviously using guns is going to ruin your chances of a perfect score.
On the other hand, I really like stealthing around with silenced weapons and/or sniper rifles in games like Goldeneye and STALKER. STALKER does let you sneak up and backstab enemies with your knife, but the programming is a little broken (in the first game, at least).
A lot of FPS games, melee weapons just don't feel right, or they're only there as an "out of ammo" option. System Shock 2 did a heck of a great job with melee, but most other games, it feels like you are basically firing a gun that only shoots a distance of a foot or two (which, technically, is how they are programmed).
_________________
I'll brave the storm to come, for it surely looks like rain...