In my view, the main problem with ranged units is that there is significantly reduced risk when using them. Being able to slaughter things from two tiles away, with focused fire, just makes the game way too easy. Personally, while I understand the two-tile range mechanic, I've often felt that it's a little ridiculous. (Don't even get me started on longbowmen). Melee just move too slowly, and when forest and hills can just shut you down, ranged units are the "go to" choice. Why use slow-moving melee, when you can just fire arrows from two city lengths away?
I always liked the ranged mechanics in the old Shogun boardgame. Archers didn't kill things from miles away (like in Civ). Instead, they were subject to the same range restrictions as your melee units (they were all part of the same army). The difference, though, was that ranged units got a "free" shot at the enemy units at the start of every battle round, simulating their safe attack distance.
I've often wondered how Civ V would play if something similar were introduced, where ranged units could only attack one tile away (like melee), but got a free shot every combat round to simulate this safe firing position. Sure, some strategic "depth" might be lost, as true ranged combat would be limited to air units, bombs, etc., and certainly units strengths would have to be reworked, but I wonder if the balance between melee and ranged would be improved. I wouldn't even mind a special stacking rule, that would allow one ranged unit to be stacked with a melee unit (but no more). It's currently just way too easy to position/use ranged units on a map that is so constricted by 1-hex movement. It would also be less ridiculous--no more longbows hitting targets ten lightyears away.