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

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?

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

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"
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?

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

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

On Apr 5, 6:24 pm, "Jim Cone" wrote:
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, USAhttp://www.realezsites.com/bus/primitivesoftware

"Punsterr"
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?