On Fri, 6 Jan 2006 14:13:02 -0600, grahamhurlburt
<grahamhurlburt.218bz0_1136578515.2777@excelforu m-nospam.com wrote:


I have tables running down a spreadsheet that are 3 columns across like
so:

dd-008 etc are just names of boards.

| dd-008 | acrylic | 0 |
| dc-004 | acrylic | 20 |
| db-012 | merlex | 40 |
| da-006 | acrylic | 0 |

There are breaks between the tables..like this one

| dd-004 | acrylic | 10 |
| dc-002 | merlex | 0 |
| da-010 | merlex | 20 |
| da-016 | acrylic | 5 |

My question is, without deleting the row, is there anyway to delete the
values in the rows with a zero and move all the values below it up?

For example, the two tables would then look like this:

| dc-004 | acrylic | 20 |
| db-012 | merlex | 40 |
| dd-004 | acrylic | 10 |
| da-010 | merlex | 20 |

There are breaks between the tables..like this one

| da-016 | acrylic | 5 |

I have worked hours on this with no luck...any ideas?

It's easy to filter out the blank and non-zero rows. But I don't understand
the logic in the filtered list having a break prior to the last row ???

In any event, label your columns. Let's call them:

Label Type Quantity

Then select the ENTIRE table, including the breaks between them.

Then Data/Filter/Autofilter

If you just want to remove the 0's, click on the down arrow next to quantity
and select Custom; then Quantity does not equal 0

Label Type Quantity
dc-004 acrylic 20
db-012 merlex 40

dd-004 acrylic 10
da-010 merlex 20
da-016 acrylic 5

If you also want to filter the breaks, with a column other than quantity, click
on the down arrow and select "non-blanks"

Label Type Quantity
dc-004 acrylic 20
db-012 merlex 40
dd-004 acrylic 10
da-010 merlex 20
da-016 acrylic 5


--ron