hiding a row in a spreadsheet if calculated value = 0

[electricbluelady]

Hello,
I am working on invoices for my employer. Each row in the spreadsheet has a
calculated value. When the calculated value in the row = 0, then I am to
'hide' the row. If the calculated value is not =0, (could be positive or
negative), then I am to display or 'unhide' the row. Does anyone know how to
do this? Macros and VBA responses are ok.

 

[Bernie Deitrick]

Electricbluelady,

Select the cells with the values (including the header row), then use Data / Filters....
AutoFilter. Select the dropdown on the header cell, then choose Custom, Does Not Equal, and enter a
0 into the right hand box. Press OK and you are done.

HTH,
Bernie
MS Excel MVP

 

[Dave]

Hi,
Try this macro:

Sub HideZeros()
A = 1 'Start Row
B = 1 'Calculated Value Column
C = 1 'Any Column which has text data in each row
Do Until Cells(A, C) = ""
If Cells(A, B).Value = 0 Then Rows(A).EntireRow.Hidden = True
A = A + 1
Loop
End Sub

You will have to change the values of A, B, C to suit your data.
A is your first Row of data (Row number)
B is the Column Number of your calculated value. This needs to be a number,
not a letter. eg, enter 5 for Column E
C is any column that has text for every row of your data. The macro
continuously checks this column for data. When it finds a blank, it will
stop. As above, enter the Column number, not the letter.

This macro just runs once, hiding rows with zero in the critical cell. If
you want a row to hide as soon as its critical cell becomes zero, then you
need an event macro, which can also be done.

Regards - Dave.