Hide column and rows based on values

[StorminASU]

Hello everyone, I have a workbook that needs to hide columns if a cell value is less than 0 and, as something entirely separate, hide numerous rows if nothing is displayed in them. Both of these will be pulling their conditional cells from cells with formulas in them. For example, the macro which will decide to hide rows will be targeting cells that have formulas which are set to "" if they need to be hidden. Same thing with the column.

Currently I'm using this code, but it doesn't seem to be updating as the formula in W10 changes. Here is the formula in W10 now: "=IF(W18>0,"TRUE","FALSE")"

Here is the code to hide the column, which is working, but not as the formula changes:

Private Sub Worksheet_Change(ByVal Target As Range)    If Not Application.Intersect(Target, Me.Range("W10")) Is Nothing Then
        If Target.Cells(1).Value = "TRUE" Then
            Me.Columns("w").Hidden = False
        Else
            Me.Columns("w").Hidden = True
        End If
    End If
    
End Sub

In addition to not knowing how to get the column to hide/unhide more automatically based on the formula results, I have no clue how to hide/unhide the rows. For values in B69:B100, if the formula result is "", the row needs to be hidden. The formula in column B is as follows, with a blank row between each 5 year increment.
=IF(B68="","",IF(B68=0,0,IF(B68+1>100,"",B68+1)))


Thank you very much for any help you can offer!

 

[Yongle]

Test this to see if is what you want
- the conditions are evaluated whenever a change in the sheet triggers the macro
- assumes W10 returns Excel boolean True or False (NOT strings - see post#2)
- the conditions in brackets equate to ether True or False which is used to determine .Hidden status in both cases

Private Sub Worksheet_Change(ByVal Target As Range)
[COLOR=#006400][I]'if W10 is True then hide column W otherwise make it visible[/I][/COLOR]
    Columns("W").Hidden = (Range("W10") = False)
[COLOR=#006400][I]'hide row if there is a value in column B for B69:B108[/I][/COLOR]
    Dim cel As Range
    For Each cel In Range("B69:B108")
         cel.EntireRow.Hidden = (cel.Value > "")
    Next cel
End Sub

 

[StorminASU]

Test this to see if is what you want
- the conditions are evaluated whenever a change in the sheet triggers the macro
- assumes W10 returns Excel boolean True or False (NOT strings - see post#2)
- the conditions in brackets equate to ether True or False which is used to determine .Hidden status in both cases

Private Sub Worksheet_Change(ByVal Target As Range)
[COLOR=#006400][I]'if W10 is True then hide column W otherwise make it visible[/I][/COLOR]
    Columns("W").Hidden = (Range("W10") = False)
[COLOR=#006400][I]'hide row if there is a value in column B for B69:B108[/I][/COLOR]
    Dim cel As Range
    For Each cel In Range("B69:B108")
         cel.EntireRow.Hidden = (cel.Value > "")
    Next cel
End Sub

Thank you again Yongle.

I changed the condition for the rows to be equal to "NA" because I had blank rows in between each increment of 5 years and this was causing those to hide. Once I changed the code, however, the rows are no longer hiding.

The formula in the cell is: =IF(B68="NA","NA",IF(B68=0,0,IF(B68+1>100,"NA",B68+1)))

The code is:

Private Sub Worksheet_Change(ByVal Target As Range)'if W10 is True then hide column W otherwise make it visible
    Columns("W").Hidden = (Range("W10") = False)
'hide row if there is a value in column B for B69:B108
    Dim cel As Range
    For Each cel In Range("B69:B108")
         cel.EntireRow.Hidden = (cel.Value = "NA")
    Next cel
 
End Sub

Perhaps I'm still just not doing the right thing to trigger this to run? Should I link this to a button to push once I'm done entering data perhaps?

Also, when I now open the workbook, "save", "paste", "open", and other ribbon commands cannot be selected.

I can't thank you enough for your help Yongle. Is there any way to offer you payment through this forum?

 

[Yongle]

Your amended code works perfectly

Have you inadvertently disabled events or does the macro reset button need pressing ?

Easiest way to test is to quickly close Excel completely and then reopen the workbook

 

[StorminASU]

Your amended code works perfectly

Have you inadvertently disabled events or does the macro reset button need pressing ?

Easiest way to test is to quickly close Excel completely and then reopen the workbook

I didn't know there was such a thing as a macro reset button!

That seemed to do the trick. Here is the code that ended up working in case anyone else needs this in the future. In the end, I made two cells with data validation (TRUE, FALSE) that I reference to either show or hide the columns I needed (ended up having 2 columns to hide instead of just W. I also changed the format and layout.

Private Sub Worksheet_Change(ByVal Target As Range) 'if W10 is True then hide column W otherwise make it visible
    Columns("T").Hidden = (Range("X3") = False)
    Columns("V").Hidden = (Range("X4") = False)
'hide row if there is a value in column B for B69:B108
    Dim cel As Range
    For Each cel In Range("B69:B108")
         cel.EntireRow.Hidden = (cel.Value = "NA")
    Next cel
 
End Sub

I don't think I can get this to work with the workbook protected though, but will be looking for a workaround. I can't thank you enough!

 

[Yongle]

Very lazy
- but test to see if causes you any issues
- amend the password

Private Sub Worksheet_Change(ByVal Target As Range) 
Const PW = "[COLOR=#ff0000][I][B]password[/B][/I][/COLOR]"
    Me.Unprotect PW
    Columns("T").Hidden = (Range("X3") = False)
    Columns("V").Hidden = (Range("X4") = False)
'hide row if there is a value in column B for B69:B108
    Dim cel As Range
    For Each cel In Range("B69:B108")
         cel.EntireRow.Hidden = (cel.Value = "NA")
    Next cel
    Me.Protect PW
End Sub