Macro to hide/unhide rows based on column value

[tlc53]

Hi there,

I've tried a couple of different codes but can't quite pull it together.

In Columns G15:G49 and H15:H49 I have formulas. If both cells in the columns equal zero (eg. G15 & H15), I would like to hide that row (row 15). If one is more than zero, it should be visible. If the data changes later, I would like them to unhide/hide accordingly.

Can someone help me please?

Thanks!

 

[Exceladd1ct]

Add this code to your WorkSheet. (Right click on sheet name-> View Code and paste:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$G$15" Or Target.Address = "$H$15" Then
        Call HideRow
    End If
End Sub

This will look for changes in those 2 cells. If changes are made, it will call HideRow function, which you should place in a new module:

Sub HideRow()
If Cells(15, "G").Value = "" And Cells(15, "H").Value = "" Then
MsgBox "Cell is Empty."
ActiveSheet.Rows("15").EntireRow.Hidden = True
Else
MsgBox "Not empty"
ActiveSheet.Rows("15").EntireRow.Hidden = False
End If
End Sub

Still, if row is hidden, unless you insert values by formula or macro, you will have to un-hide it manually.

 

[Fluff]

If you hide the row, how are the cells changing?
Do they contain formulae?

 

[tlc53]

Yes, columns G and H contain formulas which transfer figures located on another sheet.

What I would like the marco to do, is unhide all rows and then hide the rows if the formulated values of both G & H are nil/0. If it's easier, I could sum G & H in column I and then hide column I. Then only one column would need to be looked at.

Can it work on formulated cells?

 

[Fluff]

If you put the Sum in col I & use this

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Cl As Range, keyCells As Range
    Set keyCells = Range("I15:I49")
    On Error Resume Next
    Set keyCells = Application.Union(keyCells, keyCells.Precedents)
    On Error GoTo 0
    If Not Application.Intersect(keyCells, Target) Is Nothing Then
      For Each Cl In Range("I15:I49")
        Cl.EntireRow.Hidden = IIf(Cl = 0, True, False)
      Next Cl
    End If
End Sub

It's a trick I recently picked up from @mikerickson.

 

[tlc53]

Thank you. Probably a silly question but, how do I make the code run? When I press play under VBA it just brings up a list of my other marcos..

 

[tlc53]

I now see this code runs if I click in any of the cells in the range, I15:I49. The only thing is, this sheet is used to produce a report and is completely formulated requiring no user input (it collates its information from the previous sheet). So ideally, I would like to be able to prompt when it runs.
Eventually, it will form part of the code which exports the report to pdf automatically, so the user won't ever see this sheet. Only the pdf report which they will run by a macro button on the previous sheet. I've got all that code together. It's just this tidying up of the rows that I'm struggling with and then I somehow need to tie everything altogether (taking one step at a time though!).

I appreciate you/England are currently sleeping

 

[Fluff]

In that case, how about

Sub tlc53()
   Dim Cl As Range
   For Each Cl In Sheets("[COLOR=#ff0000]Sheet1[/COLOR]").Range("I15:I49")
     Cl.EntireRow.Hidden = IIf(Cl = 0, True, False)
   Next Cl
End Sub

Change sheet name to suit

 

[tlc53]

Thanks. However, it's not running. It's coming back with Run-time error '9'. Subscript out of range.

Sub tlc53()
Dim Cl As Range
For Each Cl In Sheets("Sheet81").Range("I15:I49")
Cl.EntireRow.Hidden = IIf(Cl = 0, True, False)
Next Cl
End Sub

 

[Fluff]

That means you don't have a sheet called Sheet81.
If it appears as Sheet81(Somename) in the VBA project window use, it's the codename & used like

For Each Cl In Sheet81.Range("I15:I49")

Otherwise if it's the name that appears on the sheet tab, check for leading/trailing spaces.