Macro to hide rows based on result of formula

[mstarr388]

Hello All,
I am in need of some help in writing a macro to hide rows of a spreadsheet based on the outcome of a formula.

Here is the situation:
In a separate spreadsheet (within the same workbook) names "pricing and margin sheet" I have a number of input criteria. There is a piece of equipment listed in each column in the range of AG10:AW24. In the range AG24:AW24, each cell is a drop down list where you can select "yes" or "no." This input is used in a formula in the spreadsheet labeled "quote" that will determine id the row is populated or not.

In the spreadhseet "quote" there is a table with the range of B23:I50. The "yes" or "no" input from the "pricing and margin sheet" sheet dictates the outcome of the "quote" sheet range B23:B50. Here is the formula in that row.

=IF('pricing and margin sheet'!$AG$24='pricing and margin sheet'!$Q$12,"",'pricing and margin sheet'!$AG$16)

The cell Q12 contains "no." This formula means that if the input range in the "pricing and margin sheet" says "no," then the formula enters "", if the cell says anything other than "no", the formula will refer to cell AG16.

I need the macro to hide all rows where that formula (in range B23:B50) outputs a ""

Each row (between rows 23 and 50) should be hidden if the output of all formulas in range B23:B50 is "" (in other words, if the user selects "no" for all items).

Thank you!

 

[mstarr388]

Thanks,
Here is what is in the module.

Public Sub mstarr()
Dim i   As Long, _
    ws  As Worksheet
    
Set ws = ActiveWorkbook.Sheets("quote equip")
Application.ScreenUpdating = False
With ws
    For i = 23 To 50
        If .Range("B" & i).Value = "" Then
            .Rows(i).Hidden = True
        Else
            .Rows(i).Hidden = False
        End If
    Next i
End With
    
Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("AG24:AW24")) Is Nothing Then
    Call mstarr
End If
End Sub

This is in a separate code (not module):

Public Sub mstarr1()
Dim i   As Long, _
    ws  As Worksheet
    
Set ws = ActiveWorkbook.Sheets("quote equip")
Application.ScreenUpdating = False
With ws
    For i = 56 To 63
        If .Range("B" & i).Value = "" Then
            .Rows(i).Hidden = True
        Else
            .Rows(i).Hidden = False
        End If
    Next i
End With
    
Application.ScreenUpdating = True
End Sub

I am not getting an error anymore, but it is not auto-updating. The module is saved as "Module1"

Thanks!!

 

[MrKowz]

Put both mstarr macros in the module, and place the worksheet_change macro in the worksheet code (the one where you right-click>view code)

 

[mstarr388]

Great, Thanks.

One final question. This auto-updates cells AG24:AW24. How do I get the rows to auto update based off up cell T25:AD2 in addition to AG24:AW24

So cells 'pricing and margin sheet' T25:AD25 dictate cells 'quote equip' B23:B33 and cells 'pricing and margin sheet' AG24:AW24 dictate cells 'quote equip' B35:B50

Thanks

 

[mstarr388]

Can anyone give me a hand on this?

 

[MrKowz]

Try this. I consolidated everything down into a single Worksheet_Change event, so there is no need to have the mstarr macros:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i   As Long, _
    ws  As Worksheet
    
Set ws = ActiveWorkbook.Sheets("quote equip")
If Not Intersect(Target, Range("AG24:AW24")) Is Nothing Then
    With ws
        For i = 35 To 50
            If .Range("B" & i).Value = "" Then
                .Rows(i).Hidden = True
            Else
                .Rows(i).Hidden = False
            End If
        Next i
    End With
ElseIf Not Intersect(Target, Range("T25:AD25")) Is Nothing Then
    With ws
        For i = 23 To 533
            If .Range("B" & i).Value = "" Then
                .Rows(i).Hidden = True
            Else
                .Rows(i).Hidden = False
            End If
        Next i
    End With
End If
End Sub