7

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
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
user1917946
  • 71
  • 1
  • 1
  • 2

4 Answers4

8

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
Andy Brown
  • 5,309
  • 4
  • 34
  • 39
  • Thank you for reply, but my workbook is protected and wants in protected mode only, its run in non protected mode, please help. – user1917946 Dec 20 '12 at 09:00
  • Sorry, I didn't understand the comment! I think it may be a translation problem ... – Andy Brown Dec 20 '12 at 15:14
  • @AndyBrown I have a similar issue... Please check my question here: http://stackoverflow.com/questions/19976469/unable-to-hide-a-column-in-excel-97-2003-workbook – Xavier Nov 14 '13 at 13:08
  • As I said in my answer, keep in mind that "you have a comment in one of the selected cells" may also apply to comments in a completely different column. If you hover over the comment and the box of the comment goes into the column you're trying to hide (and you're hiding everything to the right), you will receive this error. Hopefully this saves people some debug time (45 minutes for me). – user1274820 Jun 02 '21 at 22:42
2

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.

user1274820
  • 7,786
  • 3
  • 37
  • 74
0

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 :)

Alex Pan
  • 4,341
  • 8
  • 34
  • 45
Krystian Tambur
  • 1
  • 1
0

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

jxf
  • 163
  • 1
  • 9