Delete Date Rows Based on Cell Value

[JADownie]

I have a macro that I wrote below that is not working like I expected. I do not get a runtime error, but the outcome is not what I desired.

I have 2 files; 1 main macro file and a second file that this macro opens.

I am trying to delete rows in Col L of the 2nd file based on a value in the first column.


What I wanted is for the user to input a date in the main macro file. Then after they click run the 2nd file opens and the macro deletes all rows in the file which are < the input date.

My macro seems to run fine, but no rows ever get deleted. Am I missing something here now? Thanks in advance for any help.



Sub Test()
Workbooks.Open Filename:= _
"C:\Desktop\CMS Reports\DP Export.xlsx"

Windows("DP Export.xlsx").Activate


Columns("L:L").Select
Selection.NumberFormat = "m/d/yy;@"

Dim endrow As Integer
On Error Resume Next
endrow = Sheets("DP Export").Range("L1000").End(xlUp).Row
Searchdate = [CMS Report Macro.xlsm].Range("F6").Value

For i = endrow To 1 Step -1
tdate = Cells(i, 6).Value
If IsDate(tdate) = True And tdate < Searchdate Then
Cells(i, 6).EntireRow.Delete
End If
Next i

End Sub

 

[Truiz]

Try this:

[COLOR=#333333]Sub Test()[/COLOR]
[COLOR=#333333]
[/COLOR][COLOR=#333333]
Workbooks.Open Filename:= _[/COLOR]
[COLOR=#333333]"C:\Desktop\CMS Reports\DP Export.xlsx"[/COLOR]

[COLOR=#333333]Windows("DP Export.xlsx").Activate[/COLOR]
[COLOR=#333333]Workbooks("[/COLOR][COLOR=#333333]DP Export").[/COLOR][COLOR=#333333]Sheets("DP Export").[/COLOR][COLOR=#333333]Columns("L:L").[/COLOR][COLOR=#333333]NumberFormat = "m/d/yy;@"[/COLOR]

[COLOR=#333333]Dim endrow As Integer[/COLOR]
[COLOR=#333333]On Error Resume Next[/COLOR]
[COLOR=#333333]endrow = [/COLOR][COLOR=#333333]Workbooks("[/COLOR][COLOR=#333333]DP Export").[/COLOR][COLOR=#333333]Sheets("DP Export").Range("L1000").End(xlUp).Row[/COLOR]
[COLOR=#333333]Searchdate = Workbooks("CMS Report Macro").Range("F6").Value[/COLOR]

[COLOR=#333333]For i = endrow To 1 Step -1[/COLOR]
[COLOR=#333333]tdate = [/COLOR][COLOR=#333333]Workbooks("[/COLOR][COLOR=#333333]DP Export").[/COLOR][COLOR=#333333]Sheets("DP Export").[/COLOR][COLOR=#333333]Cells(i, 6).Value[/COLOR]
[COLOR=#333333]If IsDate(tdate) = True And tdate < Searchdate Then[/COLOR]
[COLOR=#333333]Workbooks("[/COLOR][COLOR=#333333]DP Export").[/COLOR][COLOR=#333333]Sheets("DP Export").Rows[/COLOR][COLOR=#333333](i).Delete[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]Next i[/COLOR]

[COLOR=#333333]End Sub[/COLOR]

 

[JADownie]

Hi -

Thanks for your reply.

This is now giving me a runtime error on this line

Workbooks("DP Export").Sheets("DP Export").Columns("L:L").NumberFormat = "m/d/yy;@"

 

[MARK858]

What happens if you change

If IsDate(tdate) = True And tdate < Searchdate Then

to

If IsDate(tdate) = True And Clng(tdate) < Searchdate Then

or

If  Clng(tdate) < Searchdate Then

in the original code?

 

[JADownie]

I still get the same error now at this line

Workbooks("DP Export").Sheets("DP Export").Columns("L:L").NumberFormat = "m/d/yy;@"

 

[MARK858]

That line isn't in the original code which I stated in post number 4

in the original code?

 

[JADownie]

Oops sorry!


When I try these 2 now it only deletes the first header row (which I actually want to keep) but no date records get deleted

Sub Test()
Workbooks.Open Filename:= _
"C:\Desktop\CMS Reports\DP Export.xlsx"

Windows("DP Export.xlsx").Activate


Columns("L:L").Select
Selection.NumberFormat = "m/d/yy;@"

Dim endrow As Integer
On Error Resume Next
endrow = Sheets("DP Export").Range("L5000").End(xlUp).Row
Searchdate = [CMS Report Macro.xlsm].Range("F6").Value

For i = endrow To 1 Step -1
tdate = Cells(i, 6).Value
If  Clng(tdate) < Searchdate Then
Cells(i, 6).EntireRow.Delete
End If
Next i

End Sub

OR

Sub Test()
Workbooks.Open Filename:= _
"C:\Desktop\CMS Reports\DP Export.xlsx"

Windows("DP Export.xlsx").Activate


Columns("L:L").Select
Selection.NumberFormat = "m/d/yy;@"

Dim endrow As Integer
On Error Resume Next
endrow = Sheets("DP Export").Range("L5000").End(xlUp).Row
Searchdate = [CMS Report Macro.xlsm].Range("F6").Value

For i = endrow To 1 Step -1
tdate = Cells(i, 6).Value
If IsDate(tdate) = True And Clng(tdate) < Searchdate Then
Cells(i, 6).EntireRow.Delete
End If
Next i

End Sub

 

[MARK858]

Untested so test on copies of your workbooks...

Sub Test()
Dim endrow As Long
    
   Workbooks.Open Filename:= _
                   "C:\Desktop\CMS Reports\DP Export.xlsx"

    Windows("DP Export.xlsx").Activate


    Columns("L:L").NumberFormat = "m/d/yy;@"

    
    'On Error Resume Next
    endrow = Sheets("DP Export").Range("L5000").End(xlUp).Row
    Searchdate = CLng(Workbooks("CMS Report Macro.xlsm").[COLOR="#FF0000"]Sheets("YourSheetName").[/COLOR]Range("F6").Value)

    For i = endrow To 2 Step -1
        tdate = CLng(Cells(i, 6).Value)
        If tdate < Searchdate Then
            Cells(i, 6).EntireRow.Delete
        End If
    Next i

End Sub

 

[JADownie]

Thank You! I just tried this code above but now the header is still there, but ALL other rows (even those > the date in cell F6) now get deleted...

 

[MARK858]

Why in the code are you using

tdate = CLng(Cells(i, [COLOR="#FF0000"]6[/COLOR]).Value)

if the dates are in column L?