Macro to hide rows based on result of formula

mstarr388

New Member
Joined
Mar 7, 2011
Messages
9
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!
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Welcome to the forums!

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

mstarr388

New Member
Joined
Mar 7, 2011
Messages
9
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

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Give this a shot:

Code:
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

New Member
Joined
Mar 7, 2011
Messages
9

ADVERTISEMENT

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

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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:

Code:
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

New Member
Joined
Mar 7, 2011
Messages
9

ADVERTISEMENT

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

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Do you have the first macro I gave you in a module in that workbook? If so, what did you name it?
 

mstarr388

New Member
Joined
Mar 7, 2011
Messages
9
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).

Code:
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

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Put the two mstarr macros in a module, not in the worksheet code (insert>module in the vba editor)
 

Forum statistics

Threads
1,141,019
Messages
5,703,776
Members
421,315
Latest member
awaisnazir139

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top