Delete row if cells are...

[shadow12345]

Ok, I would like to achieve the following.

In my active sheet, everytime the value in col O = "Z" (i can do that bit) and (can't do this bit) the value in Q is the lowest date in that col.

Does that make sense?

If cell O = "z" then look through all of the dates in col Q (with a value of z in O) and delete the lowest


eg

Col O Col Q
a 01/01/2011
z 01/03/2011
f 01/01/2011
z 01/02/2011
t 01/01/2011
z 01/03/2011
w 01/08/2011
g 01/01/2011
z 01/02/2011

In this example it should delete the 4th and the last row

 

[ron2k]

Ok, I would like to achieve the following.

In my active sheet, everytime the value in col O = "Z" (i can do that bit) and (can't do this bit) the value in Q is the lowest date in that col.

Does that make sense?

If cell O = "z" then look through all of the dates in col Q (with a value of z in O) and delete the lowest


eg

Col O Col Q
a 01/01/2011
z 01/03/2011
f 01/01/2011
z 01/02/2011
t 01/01/2011
z 01/03/2011
w 01/08/2011
g 01/01/2011
z 01/02/2011

In this example it should delete the 4th and the last row

What would happen if, say, the 3rd row was a z and contained the date 01/04/2011 and if, say, the same 3rd row (with a z value) had a date was 01/01/2011. What should happen in each of those two cases?

 

[shadow12345]

if the 3rd row was a Z and the date was 01/04/2011
still only delete row 4 and last row


if the 3rd row was a Z and the date was 01/01/2011
delete only row 3


*i should note im not counting Col O Col Q as a row....

 

[ron2k]

if the 3rd row was a Z and the date was 01/04/2011
still only delete row 4 and last row


if the 3rd row was a Z and the date was 01/01/2011
delete only row 3


*i should note im not counting Col O Col Q as a row....

Uhmnn... Sorry, its proving difficult for me to follow the logic of deleting the lowest two dates when there are 1 date (the 3rd of month) or 2 dates ( the 3rd, and 4th of the month) higher than that in the first scenario and just delete the one lowest date when there 3 higher dates (the 2nd, 3rd and 4th)

I'm pretty sure it can be done by writing a macro that autofilters your column Q by something (criteria which I still don't undersrtand) and then deleting the visible rows. Take a look at this http://www.ozgrid.com/VBA/row-delete-criteria.htm

 

[shadow12345]

ohhhhhhh Im english... date format for me is dd - mm - yyyy

so

if the 3rd row was a Z and the date was 01/04/2011 - This is the First of April
still only delete row 4 and last row - Row 4's date is first of Feb


if the 3rd row was a Z and the date was 01/01/2011 - First of Jan
delete only row 3

 

[Peter_SSs]

Test this in a copy of your workbook. It assumes ..
- Column R is available as a helper column.
- data starts in row 2.

If assumptions are not correct and you cannot modify the code yourself, post back with more details.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> DelRws()<br>    <SPAN style="color:#00007F">Dim</SPAN> LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> Frmla <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "=IF(RC[-3]=""z"",IF(RC[-1]=MIN(IF(" _<br>            & "R2C[-3]:R#C[-3]=""z"",R2C[-1]:R#C[-1])),1,""""),"""")"<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    LR = Range("Q" & Rows.Count).End(xlUp).Row<br>    <SPAN style="color:#00007F">With</SPAN> Range("R2:R" & LR)<br>        .Cells(1, 1).FormulaArray = Replace(Frmla, "#", LR)<br>        .FillDown<br>        .Value = .Value<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>        .SpecialCells(xlCellTypeConstants, xlNumbers).EntireRow.Delete<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[shadow12345]

umm R isn't Free.... AA is the first blank column will that work?

*by that I mean of course... how do I make it work because i have tried and I can't

 

[Peter_SSs]

umm R isn't Free.... AA is the first blank column will that work?

It would, but this should do just as well and is an easier adaptation of my existing code.
2 lines added where shown.

Application.ScreenUpdating = False
Columns("R").Insert
'other code in here stays the same as it was
Columns("R").Delete
Application.ScreenUpdating = True

 

[Rick Rothstein]

ohhhhhhh Im english... date format for me is dd - mm - yyyy

A suggestion for future questions you might ask in this (or other) forums that involve dates... use example dates whose day numbers are greater than 12 so we can instantly see the date format you use.

 

[shadow12345]

Indeed that might be a good idea for future reference... I just figure my location would give it away.


And that working Peter... thanks for that.