Need Help with Hide/Unhide Rows Based on Zero Value in Column

Hi All-
I have spent hours trying to find the right code to do what I need and can't seem to find exactly what I need.

I have an excel workbook with several different sheets. I need a code for the sheet named "Budgeted Expense Statement" that works when a cell value in Column O is zero, the row is hidden. The range is A2:Q970)

I have found many codes that do that part and work just fine. However, the values in column O are based off formulas that are tied to others sheets/cells (the values are not manually entered into these cells), and so when I update values elsewhere in the workbook/sheets, it updates the values in column O and the value is no longer zero, but the rows remain hidden.

I need a code that will hide the rows if the cell value in Column O for that row is zero, but then update and unhide when the values in those cells are updated and are no longer zero. This is a workbook that I will update monthly, so I can't just run it once and be done, it needs to be done automatically every time that cell either equals zero or doesn't equal zero any longer.

I found one code (it has been days ago and I don't have it anymore) that worked, but the macro constantly ran and I couldn't type anything anywhere in the workbook because it was constantly checking if the values were zero.

I could also just filter the spreadsheet every month after I am done, but other people will be accessing this workbook and I need it to be an automatic thing because I am not sure they would understand how to even filter things to see the information they need to see.

I'd be ok with a code that I could add to a button that would update by clicking a button if that is what it needs to be.

I've tried to upload an example of just the sheet I am needing to update, but understand that the actual workbook I am using has 4-5 sheets in the workbook that the information all feeds back to a sheet much like the example sheet (but much, much larger).

Thanks in advance for your help!

Hi again,

Thanks for your very prompt feedback.

Ok, there's definitely something very strange about those asterisks appearing!

Just as a check on whether or not we're going in the right direction - in the workbook I posted, change the value of Cell C3 from 20 to e.g. 21 and see what happens. You should see a lot of #VALUE errors appear, but the rows which have zero values in Column O should be hidden.

Just trying to visualise what's happening at your end - does the compiler error message say something like "Invalid use of Me keyword"? If so, it suggests that you're inserting the code in a standard VBA CodeModule rather than in the CodeModule for the worksheet itself.

Please keep me posted.

Regards,

Greg M

Ok, attached is a working miniature version of the workbook (including formulas and additional sheets) that I am trying to make work by hiding cells that equal zero in the FY Total Column (or Column O) on the Budgeted Expense Statement sheet.

As you can see, the cells from C3:N18 in the Budgeted Expense Statement sheet are all driven by formulas based off items on the Itemized Expense sheet satisfying certain "IF" statements (basically if the object code and period name on the Itemized Expenses sheet match Column A and the column headings for C:N respectively, it will add the items together and insert them in the appropriate cell) and the FY Total Column cells is the sum per row of the columns C:N.

So, this workbook is updated monthly with new entries on the Itemized Expense sheet and the Budgeted Expense Statement sheet has 970 rows, so ideally the rows would hide/unhide as the FY total column cell values are greater or less than zero as new entries are made monthly. I really don't want to scroll through 970 rows, but they need to be there just in case there is an entry throughout the year.

If the code works to hide the rows correctly initially (like so many previous codes I have tried do), I need it to work to unhide rows that were previously hidden when adding entries on the Itemized Expenses sheet causes the FY Total column on the Budgeted Expense Statement sheet to no longer equal zero.

If you want to test if it works when updated, you can update any of the values in Column I (Check Amount) on the Itemized Expenses sheet that currently equal zero and it should make the FY Total Column (and other cells) update on the Budgeted Expense Statement sheet and hopefully unhide.

Edited to add: If you want to try it, update any of the Zeros that say "Laverne" in the Payee Column on the Itemized Expenses Sheet. I know that one will for sure make the FY Total Cell Value change and no longer be zero.