Hiding rows based on cell value

[OliviaT]

Hi,

I have the following code but it's not working.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$Z$31" Then
Rows("37:44").Hidden = True
If Target.Value < 1.2 Then Rows("37:44").Hidden = False
End If
End Sub

Could someone please tell me why it's not working? As I change the value in cell Z31, Row 37 to 44 does not hide or unhide. Thanks in advance.

 

[Domenic]

Make sure that you place your macro in the code module for your sheet (right-click the sheet tab, and select View Code), and make sure that macros have been enabled. By the way, your macro can also be re-written as follows...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$Z$31" Then
        Rows("37:44").Hidden = (Target.Value >= 1.2)
    End If
End Sub

Hope this helps!

 

[OliviaT]

Hi Domenic,

Thanks for the response. The macro is in the code module for the sheet. Macros are enabled. I tried your code on another workbook and it's working fine. I don't understand what it is in my current workbook that is blocking it from working. My current workbook is saved as marco-enabled. Any other suggestions for settings? Thanks again for your help!

 

[Peter_SSs]

I don't understand what it is in my current workbook that is blocking it from working.

Does it give an error message or just do nothing?

If error, what is it & what line?

If nothing, what happens if you put this line immediately after the 'Private Sub ..' line and change any cell value?

MsgBox "Worksheet change has fired"

 

[OliviaT]

Hi Peter,

It does nothing. I do not get any error messages. I inserted your code and got the message box, however, the rows did not hide. It appears to have affected all my existing worksheets. If I add a new worksheet to my workbook, it works on the new worksheet. If I copy my existing worksheet, the problems carry over. I have too much formatting and other data to copy over to the new worksheet. Any ideas as to the issues? Thanks so much for your help!

 

[Peter_SSs]

Do you have any other code in the workbook? For example, in the ThisWorkbook module?

If you go back to your original code for the moment, and put a BreakPoint (click in the grey margin just to the left of the code) at the very first line 'If Target.Address...' and then change cell Z31, the code should stop at that first line. Now press F8 to execute a single line of code and keep an eye on both the code and the worksheet as each line is executed (keep pressing F8 when ready to execute the next line). That may possibly turn up something.

 

[OliviaT]

Hi Peter,

No other code in the workbook. I found the issue. My target cell has a formula in it so the macro is not picking it up. If I overwrite the formula and key in a value then the macro runs fine. I tried to move my formula to another cell and reference the result to cell Z31, however, it didn't work. How do I modify the code for a cell with formula instead of a value keyed in by hand? Thanks so much!

 

[Peter_SSs]

I found the issue. My target cell has a formula in it so the macro is not picking it up.

I had considered that but took the following to indicate that you were changing the value yourself.

As I change the value in cell Z31, ..

Two options

1. Have the Worksheet_Change code monitor any cells that are entered manually & whose value affects cell Z31 & tailor the code from there. This would be my first choice but the ease of taking this approach & any specific coding would depend on the formula in Z31 and possibly other formulas as well. What is the formula in Z31?

2. Change the code from a Worksheet_Change code to a Worksheet_Calculate code. In general I would be less inclined to use such code as it may mean the code is running much more often than needed as it will run every time the worksheet re-calculates, even if cell Z31 is not influenced by that re-calculation. The code would be:

Private Sub Worksheet_Calculate()
  Rows("37:44").Hidden = (Range("Z31").Value >= 1.2)
End Sub

 

[OliviaT]

Sorry, I should have been more careful with my words. What I meant was I changed numbers in other cells to get my calculated cell to be the value that I wanted. My formula in Z31 is a formula to divide values in two other cells. However, it checks the denominator cell first to be sure it's not zero to avoid null value. Here is my formula: =IF(OR(Z27=0,Z27=""),10,ROUND(Z25/Z27,2)). My other two cells (Z25 and Z27) are formulas as well. They are auto sum formulas. They add twelve months of data to get to a total.

I can't use the Worksheet_Calculate code. It kept running then gave me an errors at the end (something about out of stack). How do I modify the other code to monitor cells that affect Z31? There is a range of cells that will affect Z31. Thanks!

 

[Domenic]

In that case, we can use Peter's first suggestion. Since Z31 depends on Z25 and Z27, which in turn depend on other cells, we can monitor those other cells.

So, let's say that Z31, along with the rows that need to be hidden/unhidden are located in Sheet1, and that B10:M10 in Sheet2 are the cells that need to be monitored. Place the following code in the code module for Sheet2...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B10:M10")) Is Nothing Then
        With Worksheets("Sheet1")
            .Rows("37:44").Hidden = (.Range("Z31").Value >= 1.2)
        End With
    End If
End Sub

If the range of cells that need to be monitored are non-contiguous (ie. B10:G10 and I10:N10), replace...

If Not Intersect(Target, Range("B10:M10")) Is Nothing Then

with

If Not Intersect(Target, Range([COLOR=#ff0000]"B10:G10,I10:N10"[/COLOR])) Is Nothing Then

Change the reference to the range of cells that need to be monitored accordingly.

Hope this helps!