Help I'm Stuck...Delete cells that don't meet ascending value criteria?

[forrie13]

I have a query that runs off a MS Access Database, the following is wha
the query
produces in Excel...note PLOT = cell(A1)

PLOT AGE V0 V7 v10 V15
S2010580B0 8 191 166.3 54.6
S2010580B0 12 255.7 344.1 316.7 215.1
S2010580B0 16 399.3 482.1 453.8 356.9
S2010580B0 21 269.7 378.4 651.4 264.3
S2010580B0 27 826.4
S2010580B0 34 1018.3

Now I want to check the columns V0, V10, and V15 to make sure that th
value is increasing.
If the value decreases say in column V0 from 399.2 --> 269.7 I want t
delete 269.7
The other problem is that the number of records (rows) change fro
query to query depending what is selected in my database, and th
volume decrease may or may not be in the same position (age), if i
occurs at all.
Unfortunatly I can't just delete the data from the database as it i
needed for another purpose.
Is there a way to do this with code in my Sub Auto_Open() so I end u
with data that looks like this

PLOT AGE V0 V7 v10 V15
S2010580B0 8 191 166.3 54.6
S2010580B0 12 255.7 344.1 316.7 215.1
S2010580B0 16 399.3 482.1 453.8 356.9
S2010580B0 21 651.4
S2010580B0 27 826.4
S2010580B0 34 1018.3

Cheers

Ca

 

[Stephen Bullen]

Hi Cam,

One way would be the following:

1. Add formulas in the four columns to the right, e.g. in G3, put the
formula:

=IF(C2<MAX(C$2:C2),-999,C2)

2. Copy G2 across and down
3. Copy the entire area from G2 right and down
4. Select C2 and Paste Special > Values
5. Edit/Replace -999 with nothing to remove them
6. Clear the extra columns created in step 2.

You should be able to record yourself doing that and convert the
resultant code to work for a veriable number of rows.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk