I have a "reset" button for users on a spreadsheet that allows them to put the data validation, formulas, formatting, and conditional formatting back in the user entry section once they've overwritten it or deleted it. I have a hidden row that contains the unaltered correct formatting and such. I want to delete the conditional formatting from the user entry area and then copy the formatting from my "reference" row and paste it over the entire user entry area.

The issue I have is that there are a few columns hidden after the user entry area that use array formulas. When I try to just copy the formatting for the whole row and paste it, I get the "cannot change part of an array" error. I think I can avoid this by only copying and pasting the formatting up to my first hidden column, but I need to do this for a lot of sheets and we will be potentially adding columns and sheets in the future.

What I'm trying to figure out is how to modify this code to locate the end of the user data (I can do whatever is needed to designate the last column to copy - I just need to know what to do) and only copy and paste to there.

Please Login or Register  to view this content.
For reference, if needed, here's the ResetUnusedRows code:
Please Login or Register  to view this content.
I hope I haven't over-explained and kept anyone from potentially responding! Let me know if I can provide any additional useful details...

Thanks for any help you can provide o great forum of Excel brilliance!