I'm not even sure this is possible anymore!

[grahamhurlburt]

I have 3 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 |

There are breaks between the tables..like this one

| db-006 | acrylic | 0 |
| ds-008 | merlex | 0 |
| dc-018 | merlex | 10 |
| dd-004 | acrylic | 20 |

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 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 |
| dc-018 | merlex | 10 |
| dd-004 | acrylic | 20 |

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

[Ron Rosenfeld]

On Fri, 6 Jan 2006 14:13:02 -0600, grahamhurlburt
<grahamhurlburt.218bz0_1136578515.2777@excelforum-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

[voodooJoe]

graham -

you left out some key info, but I made assumptions:

- are the blocks you want to keep always 4 rows?
- are the spaces between blocks always teh same number of rows?

I assumed yes. The code below will do what you want.
turn off screen updating to move faster
suggest you insert a line to copy the data in case the macro crashes or get
inturrupted - that way you don't lose it

cheers - voodooJoe

Sub X()

'id your range
Set rng = Selection

'set range values to array & clear the range
'the values are in the array
aydata = rng
rng.ClearContents

'this will track where to put the data
desrow = 1

'loop thru the array
For i = LBound(aydata) To UBound(aydata)
'if the value in the 3rd column is not equal to zero
If aydata(i, 3) <> 0 Then
'write the row to the spreadsheet
For j = 1 To 3
rng.Cells(desrow, j) = aydata(i, j)
Next j
'skip to nuext row UNLESS this is the 4th row in the block, then
skip 2 rows
If desrow / 4 = Int(desrow / 4) Then desrow = desrow + 2 Else desrow
= desrow + 1

End If

Next i


End Sub


"grahamhurlburt"
<grahamhurlburt.218bz0_1136578515.2777@excelforum-nospam.com> wrote in
message news:grahamhurlburt.218bz0_1136578515.2777@excelforum-nospam.com...
>
> 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?
>
>
> --
> grahamhurlburt
> ------------------------------------------------------------------------
> grahamhurlburt's Profile:
> http://www.excelforum.com/member.php...o&userid=29878
> View this thread: http://www.excelforum.com/showthread...hreadid=498835
>

[grahamhurlburt]

Thanks voodooJoe, you're script works perfectly on the table examples! Sorry I didn't give you more info.. I thought someone would just point me in the right direction and give me a kick..I was really excited to see a working script made up!

However, putting it into action on the actual spreadsheet has created some issues..you guys seem to know everything tho..

The ranges for the actual tables are B18:D32, B52:D83, B104:D135. There is a bunch of other stuff in between the tables, merged cells and whatnot.. Here is the script I am using, it keeps dropping the rows from the second and third tables..any ideas?

Sub X()

'id your range
Set rng = Range("B18:D32,B52:D83,B104:D135")

'set range values to array & clear the range
'the values are in the array
aydata = rng
rng.ClearContents

'this will track where to put the data
desrow = 1

'loop thru the array
For i = LBound(aydata) To UBound(aydata)
'if the value in the 3rd column is not equal to zero
If aydata(i, 3) <> 0 Then
'write the row to the spreadsheet
For j = 1 To 3
rng.Cells(desrow, j) = aydata(i, j)
Next j
'skip to next row UNLESS this is the 4th row in the block, then skip 2 rows
If desrow / 15 = Int(desrow / 15) Then desrow = desrow + 20 Else desrow = desrow + 1

End If

Next i

End Sub

[Linc]

Are there the same number of rows in each table, and between each
table? That is, do you have four rows in a table, maybe one blank row,
four more table rows, one blank row, and so forth?

[grahamhurlburt]

1st table: 15 rows
19 row space
2nd table: 32 rows
20 row space
3rd table: 32 rows

Also, underneath the product code (ex. da-004) there is sometimes a description, for example:

| dc-004 | acrylic | 20 |
|description|.......|.....|
| db-012 | merlex | 40 |
| dd-004 | acrylic | 0 |
| da-010 | merlex | 20 |

Is there anyway to add a line in the function that allows the description (always longer than 10 characters) to remain if the above product code remained? But if the above code is deleted, it would be too..

Thanks

[grahamhurlburt]

Any ideas?

[Linc]

If you're moving your information between tables, can you lump the
tables together on another worksheet and refer to those cells from the
current tables? That is, B18 in your table area would reference A1 in
the contiguous table, C18 would reference B1, D18 would ref C1. B19
references A2, C19 refs B2, D19 refs C2, and so on. B52 would
reference the row after the one B32 references.

This way you should be able to easily modify voodooJoe's code to work
on the contiguous data and have it display correctly in your tables.

[Linc]

Could you put your table data into a contiguous area (either on the
same sheet or on a separate sheet) and refer to that data from your
tables?

For instance, if the table data is on a sheet named "Tables" then B18
refers to Tables!A1 (the formula in B18 would be "=Tables!A1"), C18
refers to Tables!B1, D18 references Tables!C1. Row 19 in your original
table area references row 2 on Tables, row 20 references Tables row 3,
etc.

Original table row 32 would reference row 15 on the Tables sheet. Then
you have your gap, and your second table beginning with B52 would
reference Tables row 16, the data on row 53 refers to Tables row 17,
and so on.

You can use a modified (and now simpler) version of voodooJoe's code to
massage the contiguous table data on the Table sheet, moving data up to
fill in rows that become blank, and the original table areas, by always
reading the same rows on the Tables sheet, are automatically filled in
properly.

(Google's been coughing up hairballs trying to post my comments lately.
You may already have seen a version of this comment, which I posted
last night. If so, sorry. But I haven't seen it yet.)

[voodooJoe]

that's pretty bizarre. did it to me too.

first i thought it may be the differing rows in the 3 areas, but no. i'm
really not sure what to do.

when you check the number of rows in rng, it returns 15 - the rows in area 1
of the range.
i guess you need to sum rows in all 4 areas then loop through all 4 areas
with the aydata loop nested inside

sorry, stumped and limited time today
-vdj

"grahamhurlburt"
<grahamhurlburt.218tsm_1136601602.6527@excelforum-nospam.com> wrote in
message news:grahamhurlburt.218tsm_1136601602.6527@excelforum-nospam.com...
>
> Thanks voodooJoe, you're script works perfectly on the table examples!
> Sorry I didn't give you more info.. I thought someone would just point
> me in the right direction and give me a kick..I was really excited to
> see a working script made up!
>
> However, putting it into action on the actual spreadsheet has created
> some issues..you guys seem to know everything tho..
>
> The ranges for the actual tables are B18:D32, B52:D83, B104:D135.
> There is a bunch of other stuff in between the tables, merged cells and
> whatnot.. Here is the script I am using, it keeps dropping the rows from
> the second and third tables..any ideas?
>
> Sub X()
>
> 'id your range
> Set rng = Range("B18:D32,B52:D83,B104:D135")
>
> 'set range values to array & clear the range
> 'the values are in the array
> aydata = rng
> rng.ClearContents
>
> 'this will track where to put the data
> desrow = 1
>
> 'loop thru the array
> For i = LBound(aydata) To UBound(aydata)
> 'if the value in the 3rd column is not equal to zero
> If aydata(i, 3) <> 0 Then
> 'write the row to the spreadsheet
> For j = 1 To 3
> rng.Cells(desrow, j) = aydata(i, j)
> Next j
> 'skip to next row UNLESS this is the 4th row in the block, then skip 2
> rows
> If desrow / 15 = Int(desrow / 15) Then desrow = desrow + 20 Else desrow
> = desrow + 1
>
> End If
>
> Next i
>
> End Sub
>
>
> --
> grahamhurlburt
> ------------------------------------------------------------------------
> grahamhurlburt's Profile:
> http://www.excelforum.com/member.php...o&userid=29878
> View this thread: http://www.excelforum.com/showthread...hreadid=498835
>

[grahamhurlburt]

Linc you genious...solved all the problems...

and thanks again voodoojoe, the script runs perfectly now!