CODEWITHSUNDEEP
vbaexcel
user1917946
user1917946

Reputation: 71

Excel - Run-time error '1004': Unable to set the hidden property of the range class

I am new to scripting and I am trying to improve a existing Macro. I recorded a macro to remove duplicate and added it in a Main function which calls some other functions, but I am getting this error when I add the macro I recorded:

Run-time error '1004': Unable to set the hidden property of the range class

The code looks like

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim changed As Range
    Set changed = Intersect(Target, Range("J15"))
    If Not changed Is Nothing Then
        Range("A48:A136").EntireRow.Hidden = True
        Select Case Target.Value
            Case "Agriculture"
                Range("A48:A96").EntireRow.Hidden = False
            Case "Commercial"
                Range("A97:A136").EntireRow.Hidden = False
            Case "MDP"
                Range("A48:A61").EntireRow.Hidden = False
        End Select
        Range("J15").Select
    End If
End Sub

Upvotes: 7

Views: 26621

Answers (4)

jxf
jxf

Reputation: 303

Another possibility is rogue group boxes (as was my case). To check for this, go to Home...Find and Select...Selection Pane. Unhide all rows and columns in you worksheet. Click on each name in the Selection panel and it will reveal the control for you to verify. Unless you have changed them, all group boxes will have a name starting with "Group Box".

Upvotes: 0

user1274820
user1274820

Reputation: 8144

This breaks things

When people say "You have a comment in one of the selected cells", keep in mind that THE COMMENT CAN BE IN A DIFFERENT COLUMN.

If a comment box is over the column you're trying to hide (like if you're hiding every column to the right and you have comments in a completely different column), this is the error you'll get.

If you try to manually hide the column, you'll get a different confusing error which is something along the lines of "hiding this column will push an object off of the sheet."

The comment box a few columns over is the object.

^ This would have saved me about 40 minutes of debugging.

Upvotes: 2

Krystian Tambur
Krystian Tambur

Reputation: 1

try this :)

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveWorkbook.Unprotect "password_here"
    Dim changed As Range
    Set changed = Intersect(Target, Range("J15"))
    If Not changed Is Nothing Then
        Range("A48:A136").EntireRow.Hidden = True
        Select Case Target.Value
            Case "Agriculture"
                Range("A48:A96").EntireRow.Hidden = False
            Case "Commercial"
                Range("A97:A136").EntireRow.Hidden = False
            Case "MDP"
                Range("A48:A61").EntireRow.Hidden = False
        End Select
        Range("J15").Select
    End If
ActiveWorkbook.Protect "password_here"
End Sub

This should work for you :)

Upvotes: 0

Andy Brown
Andy Brown

Reputation: 5522

Some possible answers:

  • You have a comment in one of the selected cells
  • You have some drawn objects which don't resize with text
  • Your worksheet is protected

When you set a breakpoint on the first line of the event handler, and then press F8 to step through the macro, I'm assuming it crashes on the line:

Range("A48:A136").EntireRow.Hidden = True

Upvotes: 8

Related Questions