Hide/Unhide Rows Based on Multiple Cell Calculation in Separate Sheet

HnzMgn

New Member
Joined
May 25, 2021
Messages
4
Office Version
  1. 365
Appreciate the help in advance. I have rows in sheet 2 that I would like to hide based on values in sheet 1.
Sheet 1 B17 = FALSE hide rows 23:83 in sheet 2, if TRUE unhide
Sheet 1 B24 = FALSE hide rows 84:134 in sheet 2, if TRUE unhide
Sheet 1 B31 = FALSE hide rows 135:258 in sheet 2, if TRUE unhide
The values in Sheet 1 are based off of calculations, not manually entered updates.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
457
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi,

Welcome to Mr. Excel Forum.

You need to use Event Handler. Add Change event for worksheet as below.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    With Sheets("Sheet2")
        If Target.Address = "$B$17" And Target.Value Then
            .Range("23:83").EntireRow.Hidden = True
        ElseIf Target.Address = "$B$17" And Not (Target.Value) Then
            .Range("23:83").EntireRow.Hidden = False
        ElseIf Target.Address = "$B$24" And Target.Value Then
            .Range("84:134").EntireRow.Hidden = True
        ElseIf Target.Address = "$B$24" And Not (Target.Value) Then
            .Range("84:134").EntireRow.Hidden = False
        ElseIf Target.Address = "$B$31" And Target.Value Then
            .Range("135:258").EntireRow.Hidden = True
        ElseIf Target.Address = "$B$31" And Not (Target.Value) Then
            .Range("135:258").EntireRow.Hidden = False
        End If
    End With
End Sub
 

HnzMgn

New Member
Joined
May 25, 2021
Messages
4
Office Version
  1. 365
Thanks @Saurabhj but all T/F fields need to act independent of each other. In other words, B24 is not dependent upon the results of B17, etc. Thanks in advance!
 

HnzMgn

New Member
Joined
May 25, 2021
Messages
4
Office Version
  1. 365
This is what I had for the first section that worked but I'm unsure how to add the additional sections:

Private Sub Worksheet_Calculate()
Dim MyRange As Range
Set MyRange = Sheets("Sheet1").Range("B17")
If MyRange.Value = False Then
sheets("Sheet 2").Rows("23:83").EntireRow.Hidden = True
Else
Sheets("Sheet 2").Rows("23:83").EntireRow.Hidden = False
End If
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,319
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
VBA Code:
Private Sub Worksheet_Calculate()
   With Sheets("Sheet 2")
      .Rows("23:83").Hidden = Not Range("B17").Value
      .Rows("84:134").Hidden = Not Range("B24").Value
      .Rows("135:258").Hidden = Not Range("B31").Value
   End With
End Sub
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,319
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Forum statistics

Threads
1,141,204
Messages
5,704,951
Members
421,372
Latest member
Jamie11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top