Absolutely worth it.

I've used some for high end redemptions (e.g., Park Hyatt Sydney, Conrad Bora Bora, etc.) and they can also be great for run of the mill hotels where you just need a room and/or a base of stay for a trip (e.g., Hilton Brussels Grand Place, airport hotels, hotels near sporting events).

For the former it helps to have status at that chain so you get a good room (or if it's a special event like a honeymoon -- as it was when we went to the Conrad -- reach out to see if you can pay a reasonable upgrade fee to secure a good room). If it's the latter, it usually doesn't matter that much what kind of room you get although having status helps because you usually get a better standard room and may have free access to a lounge and/or breakfast.

That said most of my hotel staying and earning is on OPM and then the redemptions are for personal travel so the opportunity cost is truly zero or even negative in my case -- not collecting hotel points while on OPM would just be money down the drain. I don't use my hotel CC except for the annual free night -- and the only time I would ever consider transferring my AMEX MR or Chase UR to a hotel is if I needed to top up for a special high-end redemption and didn't want to buy points or pay cash