Modular PSU's dont have any connectors hooked to it to begin with, just ports for cables. So if you need a 4 pin molex connector, connect it to the PSU, then connect your drives or whatever. A Non-modular PSU has the cables hardwired into the power supply itself. When you put it into the case, all the wires are in there, if you are using them or not.
The advantage of modular is that you only use the cables you need, so the case is less messy, and improves airflow, that sort of thing. However, having more connections between the PSU and the unit being powered creates a slight voltage drop, and because of the connection the power from the PSU isnt quite as pure as it would be if there was that one less connection.
Non-modular PSUs only make the one connection at the device itself, which you cant avoid. The downside is that you need to find somewhere to stuff all those cables you arent using somewhere inside the case, which can hinder airflow and make things look messy.