Macro If Cell equals 0 then hide some rows in a Sheet

[Mariecharlotten]

Hi there,

I have a Sheet and would like to only display certain information if the cell C118 is equal to 0 (if equals to 1 then I would like to hide automatically rows 68 to 77

Can you help, not really having advance knowledge on VBA
Thanks,

 

[BrianJN1]

You might be advised to add as the first line :
Sheets(Sheet_Name).select; the Sheet name needs to be enclosed in quotes.

Sub Cell_Zero()
If Range("C118").Value = "" Then
 Rows("68:77").EntireRow.Hidden = True
End If


End Sub

 

[Rick Rothstein]

I have a Sheet and would like to only display certain information if the cell C118 is equal to 0 (if equals to 1 then I would like to hide automatically rows 68 to 77

You did not say what should happen if C118 is equal to a value other than 0 or 1, so I assumed the rows should show only when 0 is placed in C118 and be hidden if C118 contains anything other than 0. The following is event code which is needed to make this all happen automatically.

Private Sub Worksheet_Change(ByVal Target As Range)
  Application.EnableEvents = False
  Rows("68:77").Hidden = [C118<>0]
  Application.EnableEvents = True
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Mariecharlotten]

Hi Rick,
Managed to find the right code, however, because 1 or 0 is formula based it only runs if I go into cell (replaced C118 by C21) and press enter to calculate, any way to have this function embedded in the macro?
Here is the code I typed under the sheet code:
Private Sub Worksheet_Change(ByVal target As Range)
If target.Address <> "$C$21" Then Exit Sub
Select Case Range("$C$21").Value
Case Is = "2018 INSIGHTS NA COMMISSIONS": Rows("67:81").EntireRow.Hidden = True
Case Is <> "2018 INSIGHTS NA COMMISSIONS": Rows("67:81").EntireRow.Hidden = False
End Select


End Sub


Thanks,