The effects of general hardness on the aquarium are related to osmosis. A fish' cell membranes flush water in either direction to maintain a balanced water:mineral ratio. Each type of fish has a different preferred hardness. For example, the Amazonian fish like very soft water, while the African cichlids prefer harder water. If the water is too far from a fish' preferred hardness, it begins suffering from osmotic stress, as the water:mineral ratio in the cells is not kept at an ideal level for it.
Most fish don't have much of a problem with this. I have my tetras in extremely hard water, and they are doing very well (colorful, active). If stacked on top of other problems (incorrect temperature, excessive wastes), osmotic stress can mean the difference between health and illness.
Lowering hardness is not as bad as lowering pH, but it
is incredibly difficult to do. Generally, the only way I know of to do it is to mix pure water (
RO, distilled, or rain) with tap water when doing water changes. There are, as far as I know, no chemicals that you can add to a tank to remove minerals.