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@excelforu m-nospam.com wrote in
message news:grahamhurlburt.218bz0_1136578515.2777@excelfo rum-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