Hide header row if range of cells in column is 0

[jamesyvoi]

I have a sheet where I am already using a macro (tied to a button) to hide all of the rows in a range that contain 0. I'd also like to be able to hide the "header" row if all cells in that range are 0. If one or more cells is greater than 0 the header row should be shown.

Header row: 18, Range of cells C19:C174, header row 175 range of cells C176:C194, header row 195 range of cells C196:C220.

I haven't been able to find any solutions that address this problem specifically (maybe I'm searching for the wrong thing.

My code for hiding rows is below, maybe I can integrate the 2?

Sub HideRows()
    
    Dim cell As Range, HideRow As Boolean
    
    With ThisWorkbook.Sheets("QUICK QUOTE")
        For Each cell In .Range("C19:C174,C176:C194,C196:C220,E240:E242").Cells
            With cell
                If IsNumeric(.Value) Then
                    If .Value = 0 Then HideRow = True ' flag to hide
                End If
                If Not .EntireRow.Hidden = HideRow Then ' take action
                    .EntireRow.Hidden = HideRow
                End If
                HideRow = False ' reset for the next iteration
            End With
        Next cell
    End With
    
End Sub

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[jamesyvoi]

Hi there,

Below is a simplyfied copy of my spreadsheet. I am trying to hide the Rows that say "Island Tables, Market Tables, etc" if all of the items below them have a qty of 0.

Book1
	A	B	C	D	E
1	Line	Description	Quantity	Price	Extended Price
2	ISLAND TABLES
3	1	7.5ft Classic Island Table Package	0	$10.00	$0.00
4	2	- Ridge Divider Package	0	$10.00	$0.00
5	3	- Dual Lower Shelf Upgrade	0	$10.00	$0.00
6	4	- Hybrid End Upgrade	0	$10.00	$0.00
7	5	- Angled Upper Shelf	0	$10.00	$0.00
8	6	7.5ft Harvest Island Table Package	0	$10.00	$0.00
9	7	- Ridge Divider Package	0	$10.00	$0.00
10	8	- Flat Upper Shelf	0	$10.00	$0.00
11	9	7.5ft Slim Classic Island Table	0	$10.00	$0.00
12	10	- Ridge Divider Package	0	$10.00	$0.00
13	11	- Dual Lower Shelf Upgrade	0	$10.00	$0.00
14	12	- Flat Upper Shelf	0	$10.00	$0.00
15	WALL TABLES
16	1	4ft Classic Wall Table	0	$10.00	$0.00
17	2	5ft Classic Wall Table	0	$10.00	$0.00
18	3	6.5ft Classic Wall Table	0	$10.00	$0.00
19	4	8ft Classic Wall Table	0	$10.00	$0.00
20	5	9ft Classic Wall Table	0	$10.00	$0.00
21	6	10.5ft Classic Wall Table	0	$10.00	$0.00
22	7	12ft Classic Wall Table	0	$10.00	$0.00
23	8	12.5ft Classic Wall Table	0	$10.00	$0.00
24	9	14ft Classic Wall Table	0	$10.00	$0.00
25	10	14.5ft Classic Wall Table	0	$10.00	$0.00
26	11	15ft Classic Wall Table	0	$10.00	$0.00
27	12	16ft Classic Wall Table	0	$10.00	$0.00
28	13	16.5ft Classic Wall Table	0	$10.00	$0.00
29	14	20ft Classic Wall Table	0	$10.00	$0.00
30	15	22ft Classic Wall Table	0	$10.00	$0.00
31	16	23ft Classic Wall Table	0	$10.00	$0.00
32	17	24ft Classic Wall Table	0	$10.00	$0.00
33	18	32ft Classic Wall Table	0	$10.00	$0.00
34	19	40ft Classic Wall Table	0	$10.00	$0.00
35	MARKET TABLES
36	20	Classic 4x2.5 Market Table	0	$10.00	$0.00
37	21	Classic 4x3 Market Table	0	$10.00	$0.00
38	22	Classic 4x3 Market Table w/Island Style Dividers	0	$10.00	$0.00
39	23	Classic 4x3 Market Table w/Ice Drain Kit & Side Panels	0	$10.00	$0.00
40	24	Contemporary 4x3 Market Table	0	$10.00	$0.00
41	25	Harvest 4x3 Market Table	0	$10.00	$0.00
42	26	Classic 5x3 Market Table	0	$10.00	$0.00
43	27	Classic 5x3 Market Table w/Island Style Dividers	0	$10.00	$0.00
44	28	Classic 5x3 Market Table w/Ice Drain Kit & Side Panels	0	$10.00	$0.00
45	29	Contemporary 5x3 Market Table	0	$10.00	$0.00
46	30	Harvest 5x3 Market Table	0	$10.00	$0.00
47	31				$0.00
48	32				$0.00
49				Subtotal	$0.00
50				Discount	#REF!
51				Installation	#REF!
52				Shipping	#REF!
53				#REF!	#REF!
Sheet1

Cell Formulas
Range	Formula
A3:A14	A3	=AGGREGATE(2,5,A2:A$14)+1
E3:E14,E36:E48,E16:E34	E3	=C3*D3
A16:A34,A36:A48	A16	=AGGREGATE(2,5,A$15:A15)+1
E49	E49	=SUM(E3:E14,E16:E34,E36:E48)
E50	E50	=IF(COUNTBLANK(#REF!),"",E49*(#REF!/100))
E51:E52	E51	=#REF!
D53	D53	=CONCAT("Total ",#REF!)
E53	E53	=IF(ISBLANK(E50),E49,E49+E50+E51+E52)

 

[mumps]

Try:

Sub HideRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long, i As Long, fRow As Long, lRow As Long
    LastRow = Range("B" & Rows.Count).End(xlUp).Row
    With Range("B3:B" & LastRow).SpecialCells(xlCellTypeConstants)
        For i = .Areas.Count To 1 Step -1
            fRow = .Areas(i).Cells(1).Row
            lRow = .Areas(i).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            If WorksheetFunction.CountIf(Range("C" & fRow & ":C" & lRow), "0") = lRow - fRow + 1 Then
                Range("C" & fRow - 1 & ":C" & lRow).EntireRow.Hidden = True
            Else
                With Range("C" & fRow - 1 & ":C" & lRow)
                    On Error Resume Next
                    .Replace "0", "#N/A", xlWhole, , False
                    .SpecialCells(xlConstants, xlErrors).EntireRow.Hidden = True
                    .SpecialCells(xlConstants, xlErrors) = 0
                    On Error GoTo 0
                End With
            End If
        Next i
    End With
    Application.ScreenUpdating = True
End Sub

 

[jamesyvoi]

Thank you,

This works well, however if I populate any of the fields in the Quantity or Price columns using a formula (to pull data from another sheet) then it wont hide any of the rows under that category (Island Tables, etc), even if they are at 0.

 

[mumps]

Try replacing:

.SpecialCells(xlConstants, xlErrors).EntireRow.Delete

with

.SpecialCells(xlFormulas, xlErrors).EntireRow.Delete

 

[jamesyvoi]

That didn't make a difference unfortunately (you said .EntireRow.Delete, but I assumed you meant the line with .EntireRow.Hidden = True)

 

[mumps]

Oops. Should be EntireRow.Hidden=True. The macro works for me. Does it work for you?

 

[jamesyvoi]

No, it doesn't work for me when there is a formula that is referencing a different cell. I've attached an updated copy below to show what I mean.

Book1
	A	B	C	D	E	F	G
1	Line	Description	Quantity	Price	Extended Price
2	ISLAND TABLES						1
3	1	7.5ft Classic Island Table Package	1	$2.00	$2.00		2
4	2	- Ridge Divider Package	1	$10.00	$10.00		3
5	3	- Dual Lower Shelf Upgrade	1	$10.00	$10.00
6	4	- Hybrid End Upgrade	#N/A	$10.00	#N/A
7	5	- Angled Upper Shelf	#N/A	$10.00	#N/A
8	6	7.5ft Harvest Island Table Package	#N/A	$10.00	#N/A
9	7	- Ridge Divider Package	#N/A	$10.00	#N/A
10	8	- Flat Upper Shelf	#N/A	$3.00	#N/A
11	9	7.5ft Slim Classic Island Table	#N/A	$10.00	#N/A
12	10	- Ridge Divider Package	#N/A	$10.00	#N/A
13	11	- Dual Lower Shelf Upgrade	#N/A	$10.00	#N/A
14	12	- Flat Upper Shelf	#N/A	$10.00	#N/A
15	WALL TABLES
16	13	4ft Classic Wall Table	0	$10.00	$0.00
17	13	5ft Classic Wall Table	0	$10.00	$0.00
18	14	6.5ft Classic Wall Table	0	$10.00	$0.00
19	15	8ft Classic Wall Table	0	$10.00	$0.00
20	16	9ft Classic Wall Table	0	$10.00	$0.00
21	17	10.5ft Classic Wall Table	0	$10.00	$0.00
22	18	12ft Classic Wall Table	0	$10.00	$0.00
23	19	12.5ft Classic Wall Table	0	$10.00	$0.00
24	20	14ft Classic Wall Table	0	$10.00	$0.00
25	21	14.5ft Classic Wall Table	0	$10.00	$0.00
26	22	15ft Classic Wall Table	0	$10.00	$0.00
27	23	16ft Classic Wall Table	0	$10.00	$0.00
28	24	16.5ft Classic Wall Table	0	$10.00	$0.00
29	25	20ft Classic Wall Table	0	$10.00	$0.00
30	26	22ft Classic Wall Table	0	$10.00	$0.00
31	27	23ft Classic Wall Table	0	$10.00	$0.00
32	28	24ft Classic Wall Table	0	$10.00	$0.00
33	29	32ft Classic Wall Table	0	$10.00	$0.00
34	30	40ft Classic Wall Table	0	$10.00	$0.00
35	MARKET TABLES
36	31	Classic 4x2.5 Market Table	0	$10.00	$0.00
37	32	Classic 4x3 Market Table	0	$10.00	$0.00
38	32	Classic 4x3 Market Table w/Island Style Dividers	0	$10.00	$0.00
39	33	Classic 4x3 Market Table w/Ice Drain Kit & Side Panels	0	$10.00	$0.00
40	34	Contemporary 4x3 Market Table	0	$10.00	$0.00
41	35	Harvest 4x3 Market Table	0	$10.00	$0.00
42	36	Classic 5x3 Market Table	0	$10.00	$0.00
43	37	Classic 5x3 Market Table w/Island Style Dividers	0	$10.00	$0.00
44	38	Classic 5x3 Market Table w/Ice Drain Kit & Side Panels	0	$10.00	$0.00
45	39	Contemporary 5x3 Market Table	0	$10.00	$0.00
46	40	Harvest 5x3 Market Table	0	$10.00	$0.00
47	41		0		$0.00
48	42		0		$0.00
49				Subtotal	#N/A
50				Discount	#REF!
51				Installation	#REF!
52				Shipping	#REF!
53				#REF!	#N/A
Sheet1

Cell Formulas
Range	Formula
D3	D3	=G3
C3	C3	=G2
C4	C4	=G2
C5	C5	=G2
D10	D10	=G4
A3:A14	A3	=AGGREGATE(2,5,A$1:A2)+1
E3:E14,E36:E48,E16:E34	E3	=C3*D3
A16:A34	A16	=AGGREGATE(2,5,A$1:A14)+1
A36:A48	A36	=AGGREGATE(2,5,A$1:A33)+1
E49	E49	=SUM(E3:E14,E16:E34,E36:E48)
E50	E50	=IF(COUNTBLANK(#REF!),"",E49*(#REF!/100))
E51:E52	E51	=#REF!
D53	D53	=CONCAT("Total ",#REF!)
E53	E53	=IF(ISBLANK(E50),E49,E49+E50+E51+E52)

 

[Sanjeev1976]

Try with a little modification to Mumps code :

Sub HideRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long, i As Long, fRow As Long, lRow As Long
    LastRow = Range("C" & Rows.Count).End(xlUp).Row
    With Range("C3:C" & LastRow).SpecialCells(xlCellTypeConstants)
        For i = .Areas.Count To 1 Step -1
            fRow = .Areas(i).Cells(1).Row
            lRow = .Areas(i).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            If WorksheetFunction.CountIf(Range("C" & fRow & ":C" & lRow), "0") = lRow - fRow + 1 Then
                Range("C" & fRow - 1 & ":C" & lRow).EntireRow.Hidden = True
            Else
                With Range("C" & fRow - 1 & ":C" & lRow)
                    On Error Resume Next
                    .Replace "0", "#N/A", xlWhole, , False
                    .SpecialCells(xlConstants, xlErrors).EntireRow.Hidden = True
'                    .SpecialCells(xlConstants, xlErrors) = 0
                    On Error GoTo 0
                End With
            End If
        Next i
    End With
    Application.ScreenUpdating = True
End Sub