The reason it's been around for 6 years and hasn't gone anywhere is because Microsoft has been putting off the transition to 64-bit for years. XP Pro x64 was a complete nightmare, and there have been all kinds of problems with Vista with trying to use certain 32-bit apps and drivers with Vista 64-bit. The fact that Microsoft has shown basically no interest in making the transition to 64-bit simple or easy, combined with the fact that almost all PCs sold come with a 32-bit version of Windows pre-installed, means that almost nobody is running a 64-bit version of Windows (hardcore gamers excluded).
That's not to say other OS's aren't struggling a little with the transition. For example, from what I understand you have to run the 32-bit version of Firefox in Ubuntu 64-bit because there's no 64-bit version of the Flash Player plugin and Firefox can't mix and match 32- and 64-bit plugins. This isn't really Ubuntu's fault as much as it is Adobe's, but again, very few people are using a 64-bit OS, meaning Adobe has little motivation to release and maintain a 64-bit plugin.
I think Apple is probably the only company doing a really good job of making the transition seamless. They've had 64-bit support for years, you just don't notice it. Leopard is a hybrid 32/64-bit operating system that will work on both types of CPUs. Meaning my MacBook Pro with a 32-bit Core Duo can run the same version of Leopard as the new MacBook Pros with a 64-bit Core 2 Duo. There's no Leopard 32-bit or Leopard 64-bit, and it runs both 32-bit and 64-bit applications if you have a 64-bit CPU. Any Universal app will run in both 32- and 64-bit mode, on both PPC and Intel processors, meaning you don't have to worry about what kind of CPU you have and getting the right OS to go with it, or which apps will run and which ones won't.
If Microsoft could do something similar, there wouldn't be a problem. Unfortunately they've decided the best route is to release separate versions of their OS for 32-bit and 64-bit CPUs, and apparently will be doing the same thing with Windows 7.
As for the RAM issue, there are plenty of ways of getting around the limit. OS X currently supports up to 32 GB in 32-bit and most versions of Linux support at least a full 4 GB. With Windows 32-bit, part of that 4 GB is used for physical address space, which means you'll up with somewhere between 2 and 3.5 GB of usable RAM, depending on your hardware setup. Their solution for what to do if you want to use more RAM is to just buy a 64-bit OS, which of course brings up the aforementioned compatibility issues with apps and drivers.
Also, most new motherboards out there will support at least 8 GB of RAM (the good ones anyway), and many of them can use up to 16 GB. The average user won't need anywhere near this amount, but hardcore gamers and people who do a lot of multimedia work generally want at least 4 - 8 GB.
Basically, if you just want a computer for web browsing, editing documents, basic stuff like that, a 32-bit OS and 2 GB of RAM is more than enough. It's not so much of a problem for casual users as it is for people who need all the power they can get, and for them, their 32-bit OS is just holding them back.