This pic above is not a cool visual effect, but the man standing there, Peter Jackson, brought the worlds of The Lord of the Rings and The Hobbit to life, and I thank him for being the coolest director in our world.
This is a difficult question, because there are so many cool moments in all three films. We also must remember the first film opened more than a decade ago now, and the special effects are still impressive. Also difficult for me because I tend to remember characters rather than action.
But I remembered late last night a moment that did stay in my mind, and that I thought about again because they also impressed me in The Hobbit. See video clip below:
The Eagles are Coming