Thread: Code deleting all rows in error

Here is the code I'm using in an existing macro to keep only rows with
today's date in column F, however it is deleting all rows. My date format in
the column is MM/DD/YYYY. Any help you can provide is appreciated. Thanks
Joyce

Keep only rows dated today

'Dim LastRow As Long
'Dim RowNdx As Long
' LastRow = Cells(Rows.Count, "F").End(xlUp).Row
' For RowNdx = LastRow To 1 Step -1
' If StrComp(Cells(RowNdx, "F"), "=today", vbTextCompare) <> 0 Then
' Rows(RowNdx).Delete
' End If
' Next RowNdx

Sub Tester()
Dim LastRow As Long
Dim RowNdx As Long
LastRow = Cells(Rows.Count, "F").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "F").Value <> Date Then
Rows(RowNdx).Delete
End If
Next RowNdx
End Sub

HTH
--
AP

"JOUIOUI" <JOUIOUIM@Discussions.microsoft.com> a écrit dans le message de
news: 3C1542DA-721F-4F31-9EC3-374C74E9DFB5@microsoft.com...
> Here is the code I'm using in an existing macro to keep only rows with
> today's date in column F, however it is deleting all rows. My date format
> in
> the column is MM/DD/YYYY. Any help you can provide is appreciated.
> Thanks
> Joyce
>
> Keep only rows dated today
>
> 'Dim LastRow As Long
> 'Dim RowNdx As Long
> ' LastRow = Cells(Rows.Count, "F").End(xlUp).Row
> ' For RowNdx = LastRow To 1 Step -1
> ' If StrComp(Cells(RowNdx, "F"), "=today", vbTextCompare) <> 0 Then
> ' Rows(RowNdx).Delete
> ' End If
> ' Next RowNdx
>

Hi Joyce

Try this:

Option Explicit

Sub test()
Dim LastRow As Long
Dim RowNdx As Long

Application.ScreenUpdating = False

LastRow = Cells(Rows.Count, "F").End(xlUp).Row

For RowNdx = LastRow To 1 Step -1
If StrComp(Format(Cells(RowNdx, "F"), "MM/DD/YYYY"), _
Format(Now(), "MM/DD/YYYY"), vbBinaryCompare) <> _
0 Then

Rows(RowNdx).Delete
End If
Next RowNdx
End Sub


--
Best Regards
Joergen Bondesen


"JOUIOUI" <JOUIOUIM@Discussions.microsoft.com> wrote in message
news:3C1542DA-721F-4F31-9EC3-374C74E9DFB5@microsoft.com...
> Here is the code I'm using in an existing macro to keep only rows with
> today's date in column F, however it is deleting all rows. My date format
> in
> the column is MM/DD/YYYY. Any help you can provide is appreciated.
> Thanks
> Joyce
>
> Keep only rows dated today
>
> 'Dim LastRow As Long
> 'Dim RowNdx As Long
> ' LastRow = Cells(Rows.Count, "F").End(xlUp).Row
> ' For RowNdx = LastRow To 1 Step -1
> ' If StrComp(Cells(RowNdx, "F"), "=today", vbTextCompare) <> 0 Then
> ' Rows(RowNdx).Delete
> ' End If
> ' Next RowNdx
>

Try this:

Dim LastRow As Long
Dim RowNdx As Long
LastRow = Cells(Rows.Count, "F").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "F").Value <> Date Then
Rows(RowNdx).Delete
End If
Next RowNdx

Mike F

"JOUIOUI" <JOUIOUIM@Discussions.microsoft.com> wrote in message
news:3C1542DA-721F-4F31-9EC3-374C74E9DFB5@microsoft.com...
> Here is the code I'm using in an existing macro to keep only rows with
> today's date in column F, however it is deleting all rows. My date format
> in
> the column is MM/DD/YYYY. Any help you can provide is appreciated.
> Thanks
> Joyce
>
> Keep only rows dated today
>
> 'Dim LastRow As Long
> 'Dim RowNdx As Long
> ' LastRow = Cells(Rows.Count, "F").End(xlUp).Row
> ' For RowNdx = LastRow To 1 Step -1
> ' If StrComp(Cells(RowNdx, "F"), "=today", vbTextCompare) <> 0 Then
> ' Rows(RowNdx).Delete
> ' End If
> ' Next RowNdx
>