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!

 

[MrKowz]

Welcome to the forums!

To clarify, you want to hide the rows on the sheet "Quote" where B23:B50 = ""?

 

[mstarr388]

Correct. So if the formula in cell B36 returns a "" I want to hide row 36. If B45 returns a "", then I want to hide row 45. Same applies for all rows between row 23 and 50. I would also like these rows to unhide these rows once they are populated with anything but a ""

Thank you

 

[MrKowz]

Give this a shot:

Public Sub mstarr()
Dim i   As Long, _
    ws  As Worksheet
    
Set ws = ActiveWorkbook.Sheets("Quote")
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

 

[mstarr388]

Thanks! That worked great!

Is there anyway to have this automatically refresh? So if the formula changes the output from "" to something else, the row will automatically unhide without having to manually re-run the macro.

Thanks again!

 

[MrKowz]

You'll need one of two types of macros to handle this:

1. A worksheet_change event that monitors the cells you manually change
2. A worksheet_calculate event that fires every time that worksheet is calculated.

I would highly advise going with option 1, so that it limits the number of times the macro is ran.

Right click on your "pricing and margin sheet" sheet tab, and paste this code in there:

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

 

[mstarr388]

Thanks, This comes up with an error.

When I change a cell in AG24:AW24 it VBA editor comes up and says "Compile Error: Sub or Function Not Defined" then highlights the first line of code in the debugger.

Any ideas on this?

Thanks

 

[MrKowz]

Do you have the first macro I gave you in a module in that workbook? If so, what did you name it?

 

[mstarr388]

I simply "viewed code" and pasted this in. I then added another step to hide a different set of rows based on a different range. Here is the code in the "quote equip" tab (I changed the name from "quote).

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

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 then added your above code to the "pricing and margin sheet" code. I re-saved the workbook as a Macro_enabled excel file.

EDIT: This second step of code that I added worked. THe only problem I am having is with the Pricing and Margin sheet.

 

[MrKowz]

Put the two mstarr macros in a module, not in the worksheet code (insert>module in the vba editor)