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

mistatasty

New Member
Joined
Jul 21, 2021
Messages
23
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,828
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 21, 2021
Messages
23
Office Version
  1. 365
Platform
  1. Windows
Yes. I know i could use filters, but I need this to be automated
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,648
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,828
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,148,146
Messages
5,745,051
Members
423,917
Latest member
Frank1931

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