Entirerow.delete based on multiple cells

[tom]

I need to delete entire rows if the date of cells in rows ab, ac, ad
adn ar are before Dec 1, 2005.

I can do a delete based on the contents of a single cell easily enough,
but this throws me.

For example:
ab ac ad ae
------------------------------------------------
12/2/05 | 11/5/05 | 11/3/05 | 11/30/05
11/30/05 | 11/1/05 | 11/5/05 | 11/5/05

I would like the routine to delete the second row, since all dates are
before 12/1/05, but leave
the first row intact since at least one date is after 12/1/05. Of
course, the live spreadsheet includes more than just two rows.

Any help would be greatly appceciated.

Thanks

-tom

 

[Tom Ogilvy]

Dim dt as Date
Dim lastrow as Long
Dim i as Long
set dt = DateSerial(2005,12,1)
lastrow = cells(rows.count,"ab").end(xlup)
for i = lastrow to 2 step -1
if cells(i,"ab).Value < dt and _
cells(i,"ac").Value < dt and _
cells(i,"ad").Value < dt and _
cells(i,"ae").Value < dt then
rows(i).Delete
end if
Next

 

[tom]

Thanks, but can you tell me why I get a "type mismatch" on this
statement:

Dim lastrow As Long
lastrow = Cells(Rows.Count, "ar").End(xlUp)

??

Thanks

-tom

 

[Ron de Bruin]

Must be

lastrow = Cells(Rows.Count, "a").End(xlUp)

 

[tom]

Nevermind - you were missing the .row, i.e.

Dim lastrow As Long
lastrow = Cells(Rows.Count, "ar").End(xlUp) .ROW

Thanks for your help.

-tom

 

[Ron de Bruin]

Ahhhaaa

indeed .row

I believe it is bed time before I post more stupid things <g>