God...there are so many...
The Wizards or whatever the name was in Warcraft (I think 1 but it may have been 2 or even both) because of polymorph. Nothing was quite as satisfying as turning your enemy into sheep.
I loved every unit in Warhammer but I think my absolute favorite were the nob squads. A fully reinforced nob squad was a wonder to watch go to work. They were just a wall of green meat that hit anything hard and the sound of their claws going to work was great. A close second from Warhammer would be the Chaos walking tank doohickey just because it looked cool. It was fairly useless as artillery and took too much damage for late game tanking but they are just great.
Company of Heroes also sports a ton of great units. All the tanks have a very satisfying attack and I loved the infantry because you had to love them or you would lose. Above all I have to say a ranked up truck with those huge anti-air guns is my favorite. Watching that thing go to work on a field of infantry is and always will be a joy.
Honorable mention to the Witch Doctor in WC3, Aurora Bomber in CnC:Generals, APC+any infantry in CnC3, Uber spearman in Civ3 who could kill a tank, and Trebuchet in Age of Empires II (which is a difficult decision because the elephants were made of so much win).
EDIT: I can't even choose a unit in any of the Homeworlds, they were all so great. I suppose nothing will ever compare to hyperspacing a battlecruiser, frigates and a carrier filled to the brim with fighters right next to an enemy base but that's a bunch of units and not just one.
