• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Delete rows in column U that contain dates

JEHalm

New Member
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
 
JEHalm


I would change the logic of your formula

From:
Code:
  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
 
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
 
[pre]
Code:
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
 
Back
Top