1

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.

Sample

ZygD
  • 22,092
  • 39
  • 79
  • 102
user15511843
  • 11
  • 2

2 Answers2

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

  • So, the result can be achieved with the use of VBA? I should have given a more details. The excel file is meant to be worked with by a group of users, who have very basic technical skills. For this reason, I would like to avoid the use of VBA. I would rather prefer to find a solution using functions (but not the ones of 365 Office as a lot of "mere mortals", including myself, simply do not have the subscription.) – user15511843 Mar 31 '21 at 07:36
0

So basically you are doing the following:

  • Search a value somewhere in a cell
  • Hide the whole row

In MS-Word, you have the possibility to use search and replace for applying specific formatting (like put all instances of a certain word into another font), however this feature seems not to exist in Excel, so VBA will be the way to go.

Dominique
  • 16,450
  • 15
  • 56
  • 112