Wont select the row intended

Hey there,

I have created a macro to delete any row which has a date older than the date specified by the user. However for some odd reason, when the macro finds a row which has an older date, it doesnt select that date and ends up selecting a completely different row and deletes that.

My macro code is shown below and I have attached my spreadsheet. If anyone has a clue as to why its selecting the wrong row then that would be great as I am quite honestly baffled!

Sub Remove_Old_Data_New()

Dim dateDim As Date, strDate As String, foundRange As Range, rangeDel As Range, i As Long, lastRow As Long
Dim confirmDelete As String

Dim convertedDate As Long
Dim convertedFoundDate As Long

dateDim = Application.InputBox(Prompt:="Please enter the date you wish to filter from: ", _
          Title:="Date Filter", Default:=Format(Date, "DD/MM/YYYY"), Type:=1)
If dateDim Then
    strDate = Format(dateDim, "DD/MM/YYYY")
Else
    MsgBox "Action Cancelled"
End If

MsgBox "The date entered is: " & dateDim, vbInformation, "Remove Old Data"

convertedDate = CDate(strDate)

MsgBox convertedDate

ActiveSheet.Select

convertedFoundDate = CDate(Cells(5342, 14).Value)


With ActiveSheet
    For i = 2 To .UsedRange.Rows.Count
        convertedFoundDate = CDate(Cells(i, 14).Value)
        If convertedFoundDate <= convertedDate Then
            .Cells(i).EntireRow.Select
            MsgBox convertedFoundDate
            MsgBox i
            .Cells(i).EntireRow.Delete
        End If
    Next i
End With

End Sub

For some odd reason I keep getting a database error when I try to upload my spreadsheet but I'll try again once I post this thread.

Thanks,

Jag

Hi,
I don't know if this will solve your problem, but anyway if you loop trough the rows with data and delete a row when condition is met, you should start from the last row and use step -1.
Buran

Here is the code

 For i = .UsedRange.Rows.Count To 2 Step -1

Please note also that using .UsedRange.Rows.Count may cause problems if you have empty rows. For example if you have one or more empty rows (i.e rows 1 is empty and your data are in rows 2 to 6) then .UsedRange.Rows.Count will return 5 and you will not check the last row (6). Hope this is not the case in your workbook.

Oh, I should have noticed that the first time
I think the problem is in this line

.Cells(i).EntireRow.Delete

change it to this

.Cells(i,14).EntireRow.Delete

PS, changed the line to fix - you should change the line where you delete the row

By the way, you don't need to select the row. i.e.
this line is not needed at all:

.Cells(i).EntireRow.Select

I'm glad that was able to help. Don't forget to mark the thread solved.