Can I hide a row conditional upon a cell value?

[davo1805]

I want to create a sheet which will serve as a Proforma Invoice displaying
data from a Bill of Materials sheet within the same file. For any line item
where the qty is 0, I would like that row to be hidden so that the Invoice
bears only the line items relevant to the proposed supply.

 

[~L]

You could apply autofiltering to the document by selecting any cell within
your data range (so long as there are no completely blank columns or rows)
going to data > filter > Autofilter (In 2007, data tab and the filter
button).

Once autofiltering is applied, click on the quantity column's dropdown arrow
(located at the first row of data) and choose 'custom' then 'not equal to'
and input 0 (or in Excel 2007 just click the arrow then uncheck 0).

If you do have completely blank rows, consider Ron de Bruin's code examples
here:

http://www.rondebruin.nl/delete.htm

to trim your data down.

 

[Gord Dibben]

To start...........formulas cannot hide anything so you must either filter
manually or use event code.

Depends upon how the quantity is derived which event type to use.

I will assume the value is formula-derived.

Private Sub Worksheet_Calculate()
'Hide rows with formulas that return 0
Dim cell As Range
On Error GoTo endit
Application.EnableEvents = False
Application.ScreenUpdating = False
With Me.UsedRange
.Rows.Hidden = False
For Each cell In .Columns(1).SpecialCells(xlCellTypeFormulas)
If cell.Value = 0 Then cell.EntireRow.Hidden = True
Next cell
End With
endit:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP