Hide rows when quantity is zero

[Michael]

Hi, I have a spreadsheet which calculates a (long) order list. I'm looking
for a way to hide the row when the calculated quantity is zero.

Order item Station A Station B
100-123 1 3
100-124 1 1
100-125 0 0
100-126 3 3

In this example I want to be able to hide the row with 100-125 and all zero
quantities (while maintaining the formulas in that row)

Michael

 

[Tom Ogilvy]

Sub Tester2()
Dim rng As Range, rng1 As Range, cell As Range
Set rng = Range(Cells(2, 1), Cells(2, 1).End(xlDown))
For Each cell In rng
If Application.Sum(cell.Offset(0, 1).Resize(1, 2)) = 0 Then
If rng1 Is Nothing Then
Set rng1 = cell
Else
Set rng1 = Union(rng1, cell)
End If
End If
Next
If Not rng1 Is Nothing Then
rng1.EntireRow.Hidden = True
End If
End Sub

 

[Michael]

Tom,
Thanks a lot for your reply.
I'm a new at this, so I apologize in advance for any stupid questions.

When I copied and pasted the macro it my sheet it hid all the rows in my
sheet.
Is there a way to tweak this macro. I have a total quantity row from say R30
to R50. If any of these cells are 0, then hide row.

Michael

_____________________________________________

 

[keepITcool]

as an alternative to Tom's macro solution..

add an extra column.
insert a formula : sum(b2:c2)=0
copy it down..

use Data/Autofilter to filter the records where the formula is false.


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Tom Ogilvy]

Since you say you can't adjust the macro to match you actual sheet rather
than the information you provided, you need to specifically state what cells
you want checked and if any cells in that range add up to zero for a single
row, then hide that row.

Unfortunately, code has to be written to match the situation, so it would be
foolish of me to try to guess again what you actually want. Stating
specifically your situation should eliminate a series of posting while those
trying to help attempt to elicit the necessary information. For example, I
assumed A2 to the last filled cell in column A. I hid rows where the cells
in column B and C of any row added to zero. This was an assumption based on
your example:

Order item Station A Station B
100-123 1 3
100-124 1 1
100-125 0 0
100-126 3 3

The code was tested under those assumptions and worked as expected.

Now you say:

I have a total quantity row from say R30
to R50. If any of these cells are 0, then hide row.

so if any single cell in Rows 30 to 50 contains a zero, then hide those
rows?

 

[Guest]

Tom,
The sheet I use is rather big and I tried to simplify the problem, but
unfortunately I confused things a bit more.
Let me try again:

Order item Price Quantity A Quantity B Total Q Extended Price
100-123 $10 1 3 4
$40
100-124 $10 1 1 2
$20
100-125 $25 0 0 0
$0
100-126 $30 3 3 6
$180

In my real sheet there are fifty order items and 10 around 10 order items.
When the Total Q cell from R30 to R50 is zero, then hide whole row.

Thanks in advance!

Michael

 

[Tom Ogilvy]

Sub HideRows()
Dim rng As Range, i As Long, icol As Long
Rows.Hidden = False
Set rng = Cells.Find(what:="Total Q", _
LookIn:=xlValues, _
Lookat:=xlPart, _
SearchOrder:=xlRows)
If rng Is Nothing Then
MsgBox "No column labeled Total Q"
Exit Sub
End If
icol = rng.Column
For i = 30 To 50
If Cells(i, icol).Value = 0 And Not IsEmpty(Cells(i, icol)) Then
Cells(i, icol).EntireRow.Hidden = True
End If
Next
End Sub

 

[Guest]

Thanks! I'll give this a try as well

Michael

as an alternative to Tom's macro solution..

add an extra column.
insert a formula : sum(b2:c2)=0
copy it down..

use Data/Autofilter to filter the records where the formula is false.


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Guest]

Thanks Tom, you're fast!
I'll try this one out.

Michael

 

[Michael]

Tom,
You're awesome! It works like a charm.

Michael