I did find a fix in the end ... but it was pretty painful 😟
Essentially, I needed to create a new administrator login on my Mac. The newly created login should work fine.
I could back up all my preferences files by opening Mac Terminal, then using Unix commands to su to my account (command line only) and copy files to a new location.
Once logged into to your account, you can potentially look for and remove corrupt files that are causing Finder to crash. If you search Google you'll find suggestions of corrupt files on the Desktop or within System Preferences that have triggered similar problems with other upgrades. Personally I couldn't find whatever it was that was causing my system to crash though. As such, I proceeded to back up the important files on my old user account, then delete the account - creating a new (equivalently named) one that I could use going forward.
It's also helpful to unhide the Library folder on my system. Fortunately you can see it through Mac Terminal - but if trying to restore files from a Time Machine copy, then this is pretty essential.
http://www.macworld.com/article/161156/2011/07/view_library_folder_in_lion.html
Speaking of Time Machine I was pretty disappointed in Time Machine's ability to restore system files though - fine regular docs but not very clever beyond that. You can do a FULL system restore by booting up on your original Mac CD, then restoring from a backup - but then you potentially need to go through the Lion update process and Finder crash all over again.
Once my files were backed up, I deleted the user account, and created a new one with the same name/password as before. The downside was that I lost my system preferences, bookmarks, and plugins RapidWeaverplus other utilities I'd installed. I needed to reinstall these (though fortunately I'd recovered my rapidweaver preferences file - plus had all the appropriate registration keys saved in my email).
Hence I've now got a working system again. Notably, another casualty of the Lion upgrade was the free NTFS-3G driver I had installed. It either wasn't recognising PC-format external drives being plugged in or was behaving very flakily. I upgraded to Paragon NTFS (USD 20) and have been find since.
SUMMARY: New Lion OS is fine, but Apple has a lot to learn about making their systems robust and not causing unnecessary pain for users.
Maybe it wasn't Apple software that caused the corrupt file (whatever it was) as it may have been other plugins or other apps. BUT, at the very least, Apple should make Finder more robust and less dumb, so that if issues cause it problems, it can handle the situation better and smoothly recover (maybe identifying the fact there had been a crash, identifying the file that caused the issue, and putting it into some sort or quarantine).
