I would seriously advise against ditching your consoles completely, but I suppose that's more dependant on the sort of games you like to play.
Why not just buy PC versions of games you want to mod. If we use The Elder Scrolls as an example again, neither Skyrim or Oblivion have outlandish PC requirements. You don't need a super top-of-the-line computer to run them. So why not just buy them (and games like them) on PC, then hang onto your consoles for everything else? PS3 especially as many PS3 games do not get PC versions.
I enjoy modding a lot, but I would certainly lean towards preferring my consoles. They're less frustrating to use, I don't have to worry about requirements, they don't do weird things (mostly) etc. etc. Elder Scrolls, though, I always buy for PC so I can mod them. If I look through my fairly large PC library (200ish games, including downloads), less than 10 I have seriously spent time modding. A few others I've messed around with a bit, but nothing worth buying a PC version over a console version for.
tl;dr: Stick with your consoles, but have a a PC on hand for the games you want to mod, just not a super-awesome, world-exploding one.