Hiding rows with comments

**cabinetguy** · 12-15-2010, 11:18 AM

I had a macro that I use to automatically hide or expand row that don't have a value or have the word "qnt." in it. It worked great until I started adding comments to certain cells in those rows. Now I get Run-time error '1004': Unable to set the Hidden property of the Range class.

Can someone edit this macro so that it works with the comments still intact and able to be read after the spreadsheet has been collapsed?

Private Sub CommandButton1_Click()
Dim j As Long
j = Range("A1").End(xlDown).Row
Rows("2:" & j).Hidden = False
End Sub

Private Sub CommandButton2_Click()
Dim r As Range
Dim r1 As Range
Set r = Range("A1").CurrentRegion
r.AutoFilter Field:=1, Criteria1:="=", Operator:=xlOr, Criteria2:="qnt."
Set r1 = r.Cells.SpecialCells(xlCellTypeVisible)
Set r1 = r.Offset(1, 0).Resize(Rows.Count - 1, Columns.Count).Cells.SpecialCells(xlCellTypeVisible)
ActiveSheet.AutoFilterMode = False
r1.Rows.Hidden = True
End Sub

**cabinetguy** · 12-16-2010, 09:17 AM

Any suggestions?

**Leith Ross** · 12-16-2010, 12:40 PM

Hello cabinetguy,

I am unable to reproduce your problem. Can you post your workbook for review?

**cabinetguy** · 12-16-2010, 12:56 PM

Ok, attached is the stripped down version, but the collapse function should still be intact, which is what I'm concerned with.

**shg** · 12-16-2010, 01:11 PM

You could reverse the logic:

Private Sub CommandButton1_Click()
    Me.AutoFilterMode = False
End Sub

Private Sub CommandButton2_Click()
    Me.UsedRange.Offset(12).AutoFilter _
            Field:=1, _
            Criteria1:="<>", _
            Operator:=xlAnd, _
            Criteria2:="<>qnt.", _
            VisibleDropDown:=False
End Sub

**cabinetguy** · 12-16-2010, 01:21 PM

That works, somewhat. If I only put 1 value in, for example, if I put 1 as the value in A34 and no other values, it only collapses up to row 34. The other rows are still visible. Also, it shows the "sort" drop down for all columns on the entire spreadsheet except for column A, I don't want any drop downs arrows visible

**shg** · 12-16-2010, 02:47 PM

Do Name Manager > New,

Name: rgnA

Scope: Quote

RefersTo: =INDEX(Quote!$A:$A, ROW(Quote!$A$13)):INDEX(Quote!$A:$A, ROW(Quote!$A$341)-1)

Then,

Private Sub CommandButton1_Click()
    Me.AutoFilterMode = False
End Sub

Private Sub CommandButton2_Click()
    Me.Range("rgnA").AutoFilter _
            Field:=1, _
            Criteria1:="<>", _
            Operator:=xlAnd, _
            Criteria2:="<>qnt.", _
            VisibleDropDown:=False
End Sub

BTW, you have a lot of clutter on the sheet, including invisible objects. I suggest you copy the used range to a new workbook and start again.

**cabinetguy** · 12-16-2010, 02:59 PM

That works as far as not showing the dropdown arrows, but it still stops if only one value is put in to any cell in column A. It works great if 2 or more different parts are selected, but if only one is selected, it stops as soon as it finds that one value.

I know this sheet is cluttered bad, I am trying to clean it up a bit and get everything working before I make a new clean sheet. There are going to be 53 of these different sheets after everything is said and done.

I have a few questions on that project as soon as I get this little problem figured out, if you wouldn't mind helping me with that one. I can send a private message to ask some questions that you can answer at your leisure if you want.

**shg** · 12-16-2010, 03:19 PM

See attached.

**cabinetguy** · 12-16-2010, 03:20 PM

Attached is the actual sheet that I am working on, with 1 value in A18. When I click "Collapse", row 14-17 collapse, but 19 on down are still visible. If I put another value in any other cells, say A20, then it works properly and all row without a quantity collapse.