I tried the second option and received another 1004 error "We couldn't do this for the selected range of cells. Select a single cell within a range of data and try again."
I then tried the first option and got the "Subtotal method of Range class failed" message.
(I did cut and paste your code example, not retype)