I did use it for some macro shots, but only very recently became enthusiastic about its use when doing night architecture shots with long exposures. Normally in the dark it's very difficult to get your focus exactly right. The viewfinder is just too dark, and AF unreliable. In these circumstances live view is great! Focussing is very easy then, just by magnifying the area you want to be in focus and manually adjust the focus. Did a series this way of the buildings on the university campus where I work, almost all with the Canon 40D live view:
http://www.pbase.com/hiero/uithofnightstudies (except "Limy" and "Tomorrow's stars")