Ytukyg

Sub hide()

    Dim wb As Workbook

    Dim ws As Worksheet

    Dim c As Range

    Dim targetRange As Range



    Set wb = ThisWorkbook

    Set ws = wb.Sheets("Ballast Quote")

    Set targetRange = ws.Range("A20:A30")



    targetRange.EntireRow.Hidden = False



    For Each c In targetRange.Rows

       If (WorksheetFunction.CountIf(c, "<>0") - WorksheetFunction.CountIf(c, "") = 0) And (WorksheetFunction.CountA(c) - WorksheetFunction.Count(c) = 0) Then

           c.EntireRow.Hidden = True

       End If

    Next c



End Sub

This macro works perfectly but i would rather not have to go in and hit "F5" every time a range of cell changes. My issue is that the cells that trigger the macro are not changed manually but are a result of another sheet, example cell A20 shows "=Inventory!$L$41". I have not found any answers on this on options to have a button or if cell is changed manually. Any takers???

Use the Worksheet.Change Event on your inventory worksheet to test if the original data changed:

'this must be in Inventory worksheet!

Private Sub Worksheet_Change(ByVal Target as Range) 

    If Not Intersect(Target, Me.Range("L41")) Is Nothing Then     

        hide 'run your procedure to hide

    End If

End Sub

This tests if Inventory!$L$41 changed and if so it runs your hide procedure.

I suggest looking into Events, these are usually Sub or function called whenever something happens to a sheet/workbook

The particular event you want is: Worksheet.Change (https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.tools.excel.worksheet.change?view=vsto-2017)

Or Worksheet.Calculate (https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.tools.excel.worksheet.calculate?view=vsto-2017)

You can hook that sub to activate yours or enter your code in there. The documentation is in C# but uses the same Events, properties and methods