Yes, it does more good than harm. If it didn’t exist a lot of games would be unplayable and stuff like anime and manga would be way less popular in the west.
…but it still does some harm in certain cases. If it was decently regulated (i.e. you can freely download stuff that isn’t currently being sold in your language through official channels) the harms might outweigh the pros, but since an English-speaking person downloading a translated rom for Torneko no Daibouken is still considered “piracy”, we definitely need it as things currently stand.