Hide/unhide rows with zero value cells or blanks across multiple sheets

Question

Scope: to hide rows containing cells with zero value and/or blanks and to unhide rows, once the same cells get populated (number, text; most of the cells contain formulas).

Required flexibility: to be able to apply hide/unhide to different specific ranges in a single sheet as well as to an entire sheet (no ranges' selection.) General: Workbook consists of multiple sheets. Cells with zero values are placed in a column with the header, while blanks are in the same column but no headers.

About data sample, A1:C12, Sheet2, workbook “File R1”
Cells of concern are column C, C1:C12 (numbers and blanks.)
Row starting A1 – all blanks from A1:C1.
Row starting A2, A3, A4 – text in A2:A4, B2:C4 - blanks.
Row starting A5 – all blanks from A1:C1.
A6:C12 – table, alphanumerical data brought in by the functions.
A6:C6 – table header, text.
C9 and C12 contain cells = 0.

Scenario A: C7,C8,C10,C11 – numbers, hide rows corresponding to C9 and C12 because originally cells = 0. Unhide, if value in C9 and/or C12 > 0. Do not hide rows corresponding to blanks in C1:C5. Scenario B: if in a column C of a table (C6:C12) all cells are equal to zero, then hide all rows corresponding to C1:C12.

As a cherry on top, it would be great to be able to run above scenarios simultaneously for more than one sheet in the same workbook. E.g., Sheet2 and Sheet3 contain identical data sets as described above.

I've come across few VBA solutions using codes. Is there a way to achieve "automation" of hide/unhide with the change in the cells' value outside VBA (and 365 Office subscription), please? Excel file is meant for a group of users with a very basic skill set.

huzaifa abedeen huzaifa abedeen · Accepted Answer · 2021-03-30T12:09:33

sub hide_unhide_rows() 

Range("1:1").Rows.Hidden = True
Range("1:1").Rows.Hidden = False

Range("1:9").Rows.Hidden = True
Range("1:9").Rows.Hidden = False

End Sub

You hide rows containing cells with zero value or blanks and to unhide rows with value, you fill the blanks with value. Also see this answer and this answer.

Dim var1 As String
Sheets("Sheet1").Select

For LR1 = 1 To Range("A65536").End(xlUp).Row
Cells(LR1, 1).Select
If (ActiveCell.Value = "") Then
var1 = Replace(ActiveCell.Address, "$", "")

Range(var1).EntireRow.Hidden = True
End If
Next LR1

Hope it helps; please upvote.

Hide/unhide rows with zero value cells or blanks across multiple sheets

2 Answers