Hiding Rows Error - "Runtime Error 1004 - Unable to set the hidden property of the range class"

[Punsterr]

Hi all,

I've searched the forum and have tried various suggestions from other
posts, but I'm still having a problem. I click on a macro button to
Hide Unused Rows, and I get the runtime error.

1) The workbook and worksheet are protected. I have included code to
unprotect the worksheet. I assume there's no need to unprotect the
workbook.
2) This macro button works fine on other worksheets within the same
workbook.
3) I'm using Excel 2003, so I don't think the focus on the macro
button is the issue.
4) I'm using the "with" code because I also have a button in the final
worksheet that calls all of the various "hide" macros for the other
worksheets.

Following is my code. You'll see that it checks columns 2, 3, 6, and
7 to see if there are any values, and if not, then it hides the row.


Sub HideUnusedRows()
Application.ScreenUpdating = False
sheets("sheetname").unprotect Password:="password"
With Sheets("Sheetname")

Dim TestRows As Integer
Dim TestColumns As Integer
Dim Count As Integer

For TestRows = 18 To 43
Count = 0
For TestColumns = 2 To 3
If .Cells(TestRows, TestColumns).Value <> 0 Then Count =
Count + 1
Next TestColumns

For TestColumns = 6 To 7
If .Cells(TestRows, TestColumns).Value <> 0 Then Count =
Count + 1
Next TestColumns

If Count > 0 Then
.Cells(TestRows, TestColumns).EntireRow.Hidden = False
Else
.Cells(TestRows, TestColumns).EntireRow.Hidden = True
End If
Next TestRows

End With

sheets("Sheetname").Protect Password:="password",
userinterfaceonly:=True, AllowFormattingCells:=True,
AllowFormattingColumns:=True, AllowFormattingRows:=True

Application.ScreenUpdating = True
End Sub

What I find interesting is that in a blank worksheet, it hides rows
18-37 just fine, but it hangs up on row 38.

Any thoughts?

 

[Jim Cone]

I ran your code in XL 97 and XL 2003 on a blank worksheet
without a problem. Rows 18 thru 43 were hidden when the
code completed.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Punsterr" <[email protected]>
wrote in message
Hi all,

I've searched the forum and have tried various suggestions from other
posts, but I'm still having a problem. I click on a macro button to
Hide Unused Rows, and I get the runtime error.

1) The workbook and worksheet are protected. I have included code to
unprotect the worksheet. I assume there's no need to unprotect the
workbook.
2) This macro button works fine on other worksheets within the same
workbook.
3) I'm using Excel 2003, so I don't think the focus on the macro
button is the issue.
4) I'm using the "with" code because I also have a button in the final
worksheet that calls all of the various "hide" macros for the other
worksheets.

Following is my code. You'll see that it checks columns 2, 3, 6, and
7 to see if there are any values, and if not, then it hides the row.

-snip-

What I find interesting is that in a blank worksheet, it hides rows
18-37 just fine, but it hangs up on row 38.
Any thoughts?

 

[Punsterr]

Thanks for your message, Jim. Okay, so the code seems to be working
fine on your system, but where does that leave me? Any suggestions on
other things I should be looking for? There must be something obvious
that I'm missing...

Rick

 

[Punsterr]

For people who may come across this thread in the future, I was
finally able to figure out what the problem was. I had to manually
experiment with hiding a bunch of rows. Turns out there was a Cell
Comment that, when the rows above it were hidden, would try to shift
off the page (for whatever reason, the Comment didn't stay in its
relative location compared with the cell that had the comment). That
would cause a "Cannot shift objects off of sheet" error. I still need
to figure out how to make the cell comment stay in its relative
location to the offending cell, but at least now I know what the issue
is.

I hope other people learn from my pain. : )