Hide rows when quantity is zero
 

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

Hide rows when quantity is zero
 

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

--
Regards,
Tom Ogilvy





"Michael" wrote in message
...
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

Hide rows when quantity is zero
 

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

_____________________________________________
"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy





"Michael" wrote in message
...
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

Hide rows when quantity is zero
 

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


"Michael" wrote:

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

Hide rows when quantity is zero
 

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?


--
Regards,
Tom Ogilvy



"Michael" wrote in message
...
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

_____________________________________________
"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy





"Michael" wrote in message
...
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

Hide rows when quantity is zero
 

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" wrote in message
...
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?


--
Regards,
Tom Ogilvy



"Michael" wrote in message
...
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

_____________________________________________
"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy





"Michael" wrote in message
...
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

Hide rows when quantity is zero
 

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

--
Regards,
Tom Ogilvy


"nospam" wrote in message
. com...
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" wrote in message
...
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?


--
Regards,
Tom Ogilvy



"Michael" wrote in message
...
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

_____________________________________________
"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy





"Michael" wrote in message
...
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

Hide rows when quantity is zero
 

Thanks! I'll give this a try as well

Michael

"keepITcool" wrote in message
...
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


"Michael" wrote:

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

Hide rows when quantity is zero
 

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

Michael

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy


"nospam" wrote in message
. com...
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" wrote in message
...
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?


--
Regards,
Tom Ogilvy



"Michael" wrote in message
...
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

_____________________________________________
"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy





"Michael" wrote in message
...
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

Hide rows when quantity is zero
 

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

Michael

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy


"nospam" wrote in message
. com...
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" wrote in message
...
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?


--
Regards,
Tom Ogilvy



"Michael" wrote in message
...
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

_____________________________________________
"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy





"Michael" wrote in message
...
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