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

 

[Lantern]

Similar to jamesyvoi's request, I have an Excel file in which part list section headers are to be hidden if no parts are selected within the section.

I need to loop through a column range that contains a formula-generated index to create a filter in the adjacent column. A part Qty may be in a 1 of many columns, so I created the Index to mark the part rows. Section rows that contain a Part Qty (as described below) are to be marked with a Y.

I've been testing my loops on a sample file that only shows an Index Column with no part information shown.

A section row starts where an Index Row = 1 and extends to where the next Index Row = 1.

Index Values:
0 = Blank Row (Desired Y/N result = N)
1 = Section Row (Desired Y/N result = Y if any parts in the section have a Qty > 0)
2 = Part Row with Qty = 0 (Desired Y/N result = N)
3 = Part Row with Qty > 0 (Desired Y/N result = Y)

The Index Row always starts at C4 and extends to the last row. There will be nothing in the file past the last parts row.

The Index Column can be a few to hundreds of rows long with a varying number of variable length sections.

The code above is close to what I need. It works very well on jamesyvoi's data. But, I have not been able to configure the Find("*" ... to recognize the the 1 in my Index column. And, I need to mark the column adjacent to my Index column with a "Y/N" instead of hiding rows.

Suggestions, please.

Thanks.

Sample file below . . .

COUNT TEST 8.xlsm
	A	B	C	D	E	F	G	H	I
1
2
3			Formula Generated Index	Desired Y/N VBA Results			Parts Row Index Key	Parts Row Description
4			1	Y			0	Blank
5			3	Y			1	Caption
6			2	N			2	Blank Part
7			2	N			3	Part w/Qty
8			2	N
9			3	Y
10			3	Y
11			2	N
12			2	N
13			3	Y
14			2	N
15			0	N
16			1	N
17			2	N
18			2	N
19			2	N
20			2	N
21			0	N
22			2	N
23			2	N
24			2	N
25			0	N
26			2	N
27			1	Y
28			0	N
29			3	Y
30			3	Y
31			3	Y
32			3	Y
33			2	Y
34			0	N
35			1	N
36			2	N
37			2	N
38			2	N
39			2	N
40			0	N
41			0	N
42			0	N
43
Sheet1

 

[jamesyvoi]

Thanks Sanjeev1976, When I tried your code the screen just flashed briefly but the lines are not hidden.

 

[mumps]

Try:

Sub HideRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long, i As Long, fRow As Long, lRow As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    With Range("B3:B" & LastRow).SpecialCells(xlCellTypeConstants)
        For i = 1 To .Areas.Count
            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

 

[Akuini]

@jamesyvoi
You're using merged cells which could cause problems, such as issues with VBA, sorting, filtering, etc. Consider using the 'Center Across Selection' option instead."

 

[mumps]

@Lantern
Hello and welcome to the Forum. It would be best if you started your own new thread.

 

[jamesyvoi]

Thanks everyone,

I realized what the issue is, if I reference a cell in the same sheet everything works properly, however if I reference a cell from a different sheet (same workbook), then it wont hide any of the lines with "0".

ie. If I use =H249 all the lines hide properly, if I use ='Sheet 2'!C32 then it wont hide the lines that have a quantity of 0.

 

[jamesyvoi]

Sorry, my previous message was incorrect, it appears even if I reference a cell in the same sheet it does not work.

 

[jamesyvoi]

Just looping back to this. I've tried all sorts of variations, but I have not been able to get this to work when the cell in column C reference another cell (=G2, etc)

Does anyone else have any ideas on how to get this working?

Thank you!