VBA Code works in one workbook but not another

[kbianco]

Hi there,

I am working with a VBA code to help clean up some formatting within my excel workbooks. I am using the below code in one workbook, but when i try to apply it to another (larger) workbook I receive an error. The two workbooks are exactly the same, except one has more sheets than the other (contents of each sheet are more or less the same).

The error message I am getting is Run-time error 1004: Unable to set the Hidden property of the Range class. It seems like it is pointing to the Hidden Row component of the code.

Sub Row_Column_workbook()
Dim ws As Worksheet
For Each ws In Worksheets
With ws
.Columns("M:S").ColumnWidth = 0.25
.Rows("46:71").RowHeight = 0.5
.Columns("T:XFD").Hidden = True
.Rows("73:1048576").Hidden = True
ActiveWindow.DisplayGridlines = False
ActiveWindow.DisplayHeadings = False
End With
Next ws
End Sub

Any insight is greatly appreciated.

Thank you,
Kayla

 

[Herakles]

Hi there,

I am working with a VBA code to help clean up some formatting within my excel workbooks. I am using the below code in one workbook, but when i try to apply it to another (larger) workbook I receive an error. The two workbooks are exactly the same, except one has more sheets than the other (contents of each sheet are more or less the same).

The error message I am getting is Run-time error 1004: Unable to set the Hidden property of the Range class. It seems like it is pointing to the Hidden Row component of the code.

Sub Row_Column_workbook()
Dim ws As Worksheet
For Each ws In Worksheets
With ws
.Columns("M:S").ColumnWidth = 0.25
.Rows("46:71").RowHeight = 0.5
.Columns("T:XFD").Hidden = True
.Rows("73:1048576").Hidden = True
ActiveWindow.DisplayGridlines = False
ActiveWindow.DisplayHeadings = False
End With
Next ws
End Sub

Any insight is greatly appreciated.

Thank you,
Kayla

I have added EntireColumn and EntireRow.

Sub Row_Column_workbook()
Dim Ws As Worksheet

    For Each Ws In Worksheets
    
        With Ws
            .Columns("M:S").ColumnWidth = 0.25
            .Rows("46:71").RowHeight = 0.5
            .Columns("T:XFD").EntireColumn.Hidden = True
            .Rows("73:1048576").EntireRow.Hidden = True
        End With
    
        ActiveWindow.DisplayGridlines = False
        
        ActiveWindow.DisplayHeadings = False
            
    Next Ws

End Sub

 

[MARK858]

Do you have any sheet protection or comments in any of the cells in the problem sheet?

 

[Herakles]

Hi there,

I am working with a VBA code to help clean up some formatting within my excel workbooks. I am using the below code in one workbook, but when i try to apply it to another (larger) workbook I receive an error. The two workbooks are exactly the same, except one has more sheets than the other (contents of each sheet are more or less the same).

The error message I am getting is Run-time error 1004: Unable to set the Hidden property of the Range class. It seems like it is pointing to the Hidden Row component of the code.

Sub Row_Column_workbook()
Dim ws As Worksheet
For Each ws In Worksheets
With ws
.Columns("M:S").ColumnWidth = 0.25
.Rows("46:71").RowHeight = 0.5
.Columns("T:XFD").Hidden = True
.Rows("73:1048576").Hidden = True
ActiveWindow.DisplayGridlines = False
ActiveWindow.DisplayHeadings = False
End With
Next ws
End Sub

Any insight is greatly appreciated.

Thank you,
Kayla

This may explain the error.

Hide a row using VBA in Excel

Hide a row using VBA in Excel

www.codevba.com

 

[kbianco]

Hi - thank you very much for your reply! I tested this version, but unfortunately still getting the same error. I do notice excel is giving a warning, number stored as text message for some of the cells in rows i want to hide. Do you think this could be impacting the code? If so, do you know a way to ignore these cell messages.

Thanks again!

 

[kbianco]

Do you have any sheet protection or comments in any of the cells in the problem sheet?

hi! thanks for the reply. there are no protected sheets or comments but i am getting a warning for "number stored as text" in some of the cells I want to hide. Do you think this could be considered a "comment"? I don't care about this error, so want to bypass using VBA if needed. Thanks!

 

[Herakles]

Hi - thank you very much for your reply! I tested this version, but unfortunately still getting the same error. I do notice excel is giving a warning, number stored as text message for some of the cells in rows i want to hide. Do you think this could be impacting the code? If so, do you know a way to ignore these cell messages.

Thanks again!

View attachment 96914
View attachment 96915

I can hide the same rows and columns for one worksheet.

Create a blank worksheet and just try the code on that one sheet without any data.

Re: Do you think this could be impacting the code?
I don't think so but try the above and see what happens.

 

[kbianco]

I can hide the same rows and columns for one worksheet.

Create a blank worksheet and just try the code on that one sheet without any data.

Re: Do you think this could be impacting the code?
I don't think so but try the above and see what happens.

Thank you. The code does work on a blank worksheet, as well as a smaller version of the workbook I am trying this on (same data in all the sheets, just less sheets). It is odd that the larger sheet is throwing an error when it is the same code and data.

 

[MARK858]

but i am getting a warning for "number stored as text" in some of the cells I want to hide. Do you think this could be considered a "comment"?

I have never come across the Numbers stored as text causing the issue but that doesn't mean that it isn't, just I have never come across it.
The other things that I have seen cause the error is grouped rows, shapes not being set to move and size with cells being in the range and merged cells .

Btw .Columns("T:XFD") and .Rows("73:1048576") already refer to entire columns and entire rows respectively

 

[kbianco]

Hi again - thank you for the input. In looking at this again, I am still getting the same error BUT when I select debug, then continue run the code works as expected. Have you encountered this type of behavior before/

Thanks!
Kayla