They're great for off-roading and racing, but outside of those use cases automatics are just better.

5-speeds are definitely a pain in the ass in stop and go traffic, but the benefits of driving a manual transmission goes beyond use cases. For example, manual transmissions will always be cheaper to repair and easier to diagnose than automatic ones. Similarly, basic 4-cylinder engines will always be easier to diagnose and repair than the highly complicated CVT engines in all the new automatic cars.

Note too that a 5-speed Honda civic made 20 years ago will easily get 45 mpg, simply because by the time you get to third gear, the car is light enough that you can just ride the clutch for the next 1,000 feet. My 05 manual civic ex gets around 40 mpg combined easily, and I'll never have to worry about potentially paying a third of the car's out the door price if the engine ever decides to self destruct. I know we're discussing transmission and not engines, but since you brought up the newer fuel efficient automatics, it seems worth noting.

It's all preference at the end of the day, though. I personally like how connected to the car you feel with manual transmissions, use cases/utility arguments aside. I also kinda wonder if manual drivers are less likely to crash, since it's difficult if not impossible to be on your phone while driving.