Corruption on worksheet range, how to clear?

I just hate it when thing are going smoothly and then all of a sudden a new problem pops up.

I have a WB with a worksheet that utilizes the first 642 rows and columns A through R. I normally hide rows 45 through the end of the worksheet at row 1048576. Now suddenly I get the error run-time err 1004 unable to set the Hidden Property of the range class.

I have been able to hide rows 45 through 103890, but something between there and the end of the worksheet is causing me problems.

I have selected the range from beginning to the end and deleted any contents, but it still fails.


I have used the following routine to clear all contents and formatting:

Please Login or Register  to view this content.

but I still get the error when trying to hide every row form 1048201 through the end of the worksheet.

Does anyone have any ideas that might help?

Deleting the entire worksheet and starting over is Not an option and do to the sensitivity of the data in the worksheet, I am unable to provide that as well.

Any suggestions are greatly appreciated ...

Generally, this is a problem with the graphics on the sheet (shapes, controls, comments).

1. Check that any graphic objects are not near the end of the sheet. They can have height and / or width = 0, so they will not be visible.
2. If there are comments in cells and you have been filtering / sorting on the worksheet, it could have made the comments prevent you from hiding rows at the end of the worksheet. Select the cells and have all comments shown. See their actual positions.

Artik

thanks for the suggestions. The only Graphic objects are buttons in the first 45 rows of the worksheet, and I do have some comments, but those also are in the first 45 rows. I do not use any filtering or sorting in this worksheet. Other than running some code that copies the first 45 rows to other locations below it, they still do not exceed row 652. If I manually select one of the rows that the code does not work on, I am able to hide that row. However, that in itself would be quite time consuming, so I create a loop routine to do that, but then it fails the same way. I may have to back up and reinvent the wheel in order to resolve this, but I have not gotten there yet. By reinventing the wheel, I mean backup to an earlier copy (28) where I am still able to hide the rows. As I develop code or edit sheets I periodically save a copy. Presently I am at copy (34). I do document changes as I make them, but occasionally I may omit something from the comments.... so going back can be time consuming.

I will try all recommendations.. and if that fails in a few days, then back to the drawing board starting with copy (28).

Thanks

I will mark this post as resolved. Although I was not able to find the source of corruption that caused the problem or a solution, I was able to rebuild from a previous copy before the problem began. Sometimes it is better to do that than waste time trying to figure out what went wrong and how to resolve it. In any case, I have recovered from the issue, which only took me about 6 hours after spending 2 days trying to resolve the original issue.

You must know "Murphy's Law"
At the most inconvenient time it will bite you again. After all, it happened for some reason and that reason will happen again.
If you still have the bad file and want to try what Artik suggested, try one of these.
If you do find the problem through this, at least you'll be able to eliminate the cause. Otherwise you'll be spending several hours again re-creating your sheet/workbook.

Please Login or Register  to view this content.

Change the H5 cell reference to a cell in an empty column.

Please Login or Register  to view this content.

Change the 10 to a Column number of an empty Column

Please Login or Register  to view this content.

Below are some tools that can be useful when analyzing the content of the sheet.

Please Login or Register  to view this content.

Artik

Thank you, I will go back and try some of these on that copy that failed... but will also add these to my toolbox... Thanks again.