I can't say I have any single one, but a few definitely stand out
The Tiger Tank from Company of Heroes, specifically the alternate model and voice one from Tales of Valour which featured in the mini-campaign 'Tiger Ace'; the commander always sounds awesome and it is a bit more refined than the other German super-weapons; unlike the King Tiger or Jagdpanther, it can be replaced if lost; it is reasonably quick for such a power vehicle, has excellent armour and is affected by the Veterancy upgrades the Wehrmacht get. In my opinion, it is disputably the best heavy armour available to any of the four factions.
Alternative...
Chaos Space Marine Squad from Dawn of War: Chaos Rising. Basic upgrade boosts their stats. The can get a champion who makes them better again. Lastly they can be upgraded to be either amazing ranged soldiers or given chain-axes (a bit like a chainsaw, but in axe form - nasty!) and used as heavy assault troops. The just look awesome as well.
Another...
Shogun: Total War's battlefield ninjas. They are Ninjas who fight on the battlefield; I shouldn't need to say more! They look cool, are stealthy and excellent in both close combat and can barrage enemy units with throwing knives. Sure the graphics haven't aged well, but the unit's profile picture is nice and, well their ninjas for goodness sake; a whole squad of NINJAS! I sincerely hope they make a return in Shogun 2.
These are the first three that came to mind, but I'd also regard a lot of Panzer Elite units from Company of Heroes: Opposing Fronts, the Monkeylord from Supreme Commander and the Glash Gitz from Dawn of War 1 as all being really cool as well, although there are a lot of units from a lot of games I like a lot.