Linda Blair make-up FX work in The Exorcist
Blood elevator in Kubrick's Shining
Terminator endoskeleton.
Tina getting twisted around in mid-air in Nightmare on Elm Street
Lawnmower scene and the rest of the gore-effects in Braindead
Arnold removing the fake head from his own in Total Recall
The warped body effects on the people in Total Recall who are exposed to the Mars athmosphere at the beginning in Arnold's dream and at the end of the movie.
Vincent Lombardi's work in ALIEN
The Alien never-see-a-rod-or-wire Queen in Aliens
ED 209 Stop Motion Animation in Robocop

Yeah, Cameron did such an amazing job of cutting the full size queen with the rod puppet it hurts. I also agree with the comment on Lombardi's work on Alien, more specifically the infamous Chestburster.
And although I'm not a big CGI fan, the battle sequence involving the "Resurrection Ship" battle in a recent episode of BSG was utterly blow away.

Keith Loh has posted just about every one I was going to say.

Thanks :)

Fight Club had an interesting way of using CG type effects which made the overall movie one of my favs :)

Damn, Keith... leave something for somebody else woodya? Nice list.

Just a note to add here. I was watching "The Italian Job" on cable yesterday. They ran a lot of the behind the scenes footage. And here's where I think the "Magic" has gone out of the movies.

When I saw the helicopter fly under the overpass, and hover... cutting off the car's escape... I thought "Okay, that part is CG... and that part is a Mock Up close up, now we're back to CG..."

Trouble was, the behind the scenes showed it was a reall chopper, being flown by a HELLUVA pilot.

Because we expect so much CG nowdays, to be virtually seamless... we fail to be amazed by the truly dangerous and skillfull stunts being performed.

The hardest thing to do, is fool another magician.

Best Visual FX I have ever seen are PJ's King Kong. The scene where he is passing out knee deep in water and he reaches for Anne Darrow, I turned to my friend (we both do CGI professionally) and he was staring back at me. Our jaws were just on the floor for that entire film.

Well,
Keith pretty much covered it...a great list that brought back many great memories.

One I would add would probably be the cape from Spawn. Of course it was obviously CG, no getting around that...but it was truly poetic to behold.

Also, who can forget the Oscar winning tranformation scene from "American Werewolf in London"?

Keith noted "Star Trek II, the Wrath of Kahn"...I remember that this movie had a profound effect on me, in that the scenes of the Genesis test site introduced me to the potentials of matte painting.

As far as non-CG effects work, I have to give major nods to the make-up artistry in the werewolves (on stilts) from "Underworld" (the 'making of' features on the DVD educated me to the fact that most of what I thought was CG was actually not - although there was still plenty of obvious CG used)

All of the Rocky movies in using 'forced perspective' to present Sly as taller than he is (remember the initial face-to-face with Dolph Lundgren in "Rocky IV"...and you thought - gee can that Russian guy really be 8 feet tall?)

John Carpenter's 'The Thing'....wow, I can't say enough about the creative use of effect in that one (although I'm going from memory as I was a teen and it has been awhile since I've seen it)

I loved the use of 'scripted colorization' in "Pleasantville" as a means to provide underlying stories throughout the movie - you see the color, you know what's goin' on.

I also have to give a nod to any scene containing Wallace & Gromit for extreme greatness in the realm of clay animation and miniatures.
-Jon

Oh, I almost forgot.

ET

The fact that a PUPPET could engender so much sympathy from the viewer...wow.

(but then there is the Neil Diamond song "Turn on your Heartlight" to make the whole thing come crashing down.)
-Jon

Terminator 2...the creative use of twins as an alternative to efx....Linda Hamilton's twin in the playground scene, and of course the guard twins in the mental asylum. No, in contrast to the thread title, these don't 'blow you away' but they are a clever way to avoid hefty efx production costs.
-Jon

I'm glad someone mentioned The Shining. The things that blew me away the most though, the way the snow looked when it was lit up along side the greenery, particularly in the maze, and the sheer size of the fabulous hotel (and seen at tricycle level for some shots!). Never before has a film given me the feeling of such a tremendous amount of space and well lit snow just has this other worldly quality for me.

Chronicals of Riddick was very visually impressive, its just a shame the writing was so bad, but in the same vein, Elektra had some very impressive shots. From the circle of petals that die at they fall, to someone falling down a well and exploding into air. Dire writing, but pure eye candy and quite breathtaking in parts.

I remember watching that ep and was quite in awe at that those ships rocketing the hell out of one another. The new BSG has been sporting some great visuals.

At the time, I was pretty impressed with "The 5,000 Fingers of Dr. Terwilliker".
For 1953, it was unprecedented and brought Dr. Seuss to life on the screen.
The cast couldn't have been better chosen and everything in the production was top-notch. Probably the only reason it hasn't been re-made, is that they could never do it again, any better than that.

I was blown away by the opening sequence in The Two Towers. A combination of a lot of things, but very cool. The final long shot of Gandalf and the Balrog's fall was stunning.

David Lynch
 

David Lynch participated in a lumiere film challenge with a camera and conditions of 1895 cinematography. The film was one take and had some very interesting special effects.

I prefer effects that don't blow me away, not on first viewing. Contact was full of them. The opening shot in the eye. The girl running/mirror shot. Effects that don't even appear to be effects at first. Only when watching making-of features did I realize they were effects, at which point I got the "wow" factor. That level of seamlessness is what impresses me.