Delete rows in column U that contain dates

[JEHalm]

I'm trying to delete any rows in column "U" that contain dates (01/01/2012). I'm using Dim P below to do this but am unscucessful. Any help is appreciated. My guess I have the incorrect data type.


Sub expended()y


Sub Over_expended()

'INSERT COLUMN J

'ALL TOTALS AT END


' Del_top_three_rows Macro

'

Rows("1:3").Select

Selection.Delete Shift:=xlUp


'Deletes all Rows that <> "5"


'If Cells(y, 12).Value < 1500 Or Cells(y, 12).Value > 1599 Then Cells(y, 1).EntireRow.Delete Shift:=xlUp


Dim x As Long

For x = Range("AT1:AT" & Range("AT" & Rows.Count).End(xlUp).Row).Rows.Count To 2 Step -1

If LCase(Trim(Cells(x, 46).Text)) <> "submitted" Then Cells(x, 2).EntireRow.Delete Shift:=xlUp


Dim p As Long

For p = Range("u1:u" & Range("u" & Rows.Count).End(xlUp).Row).Rows.Count To 2 Step -1

If LCase(Trim(Cells(p, 21).Value)) > 1 / 1 / 1990 Then Cells(p, 2).EntireRow.Delete Shift:=xlUp


Next p


End Sub

 

[Hui]

JEHalm


I would change the logic of your formula

From:

  If LCase(Trim(Cells(p, 21).Value)) > 1 / 1 / 1990 Then Cells(p, 2).EntireRow.Delete Shift:=xlUp 


To:  If Cells(p, 21).Value >= 40909 Then Cells(p, 2).EntireRow.Delete Shift:=xlUp

Where 40909 is the serial number for 1/1/2012

 

[JEHalm]

Hi Hui, thanks for the help. The code supplied works but it is doing the opposite what I wanted. Please note the code below is a little different in the columns selected for this procedure after I made some other changes.


I'm trying to delete all rows that have date values in the cells of column ak. Column ak indicate dates of completed contracts. However, the blank cells indicate that those contracts are still open, thus those cells are blank. The template will show all open contract open. I fiddled around with the date numbers with no luck. The blank cells are absoultley blanks, no dates, no numbers, nothing. Can you modify it?


Thanks, JDubs


Sub DeleteRowsSAS()


Dim p As Long

For p = Range("ak1:ak" & Range("ak" & Rows.Count).End(xlUp).Row).Rows.Count To 2 Step -1

If Cells(p, 21).Value <= 40909 Then Cells(p, 2).EntireRow.Delete Shift:=xlUp

 

[Hui]

[pre]

For p = Range("ak1:ak" & Range("ak" & Rows.Count).End(xlUp).Row).Rows.Count To 2 Step -1
If Cells(p, 37).Value <> "" Then Cells(p, 2).EntireRow.Delete Shift:=xlUp
Next p

[/pre]

Column AK is Column 37 not 21