Macro that hides rows if a cell value is not populated from an IF formula

[mistatasty]

Hi all. Would really appreciate some help with this. Currently, I have a set of information on a worksheet. The information includes project numbers and project names in columns A and B. The names and numbers in these columns are being populated through an IF statement that is referencing a pivot table on a separate sheet in the workbook. If the formulas do not pull any information from the pivot table, the IF statement shows a "0" like what is normal for an IF statement being false.

My issue is that I would like the rows that contain a "0" in column A (as a result of the IF statement not pulling anything) to be automatically hidden. Right now, I have the following code:

Sub HideRows()
Dim cell As Range
For Each cell In Range("A21:A127")
If Not IsEmpty(cell) Then
If cell.Value = 0 Then
cell.EntireRow.Hidden = True
End If
End If
Next
End Sub

Not sure why, but the macro wont run automatically. It will only hide the rows with 0's if I manually hit run in VBA. Further, if a row is already hidden due to the 0, but then new information gets added to the pivot table on the second worksheet, the row stays hidden despite being populated with the project name/number and not being a 0 anymore.

Is there any fix to this? Would be greatly greatly appreciated!

 

[Peter_SSs]

Welcome to the MrExcel board!

You can probably avoid the need for your macro by using the FILTER function in Microsoft 365. If you needed help with that you would need to show us (XL2BB preferred) what the pivot table is like and exactly what data you want pulled to the worksheet that currently has the IF formula.

 

[mistatasty]

Yes. I know i could use filters, but I need this to be automated

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file (de-sensitized if necessary), to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

 

[Peter_SSs]

Yes. I know i could use filters, but I need this to be automated

I'm not talking about using AutoFilter, I'm talking about the FILTER() function.
If you can show me what I asked for I can hopefully show you what I mean.