Delete rows with criteria - not working 2

[RP1America]

Looking to delete rows if:

1) Cell A in row does NOT equal today's or yesterday's date.

2) Cell F in row equals cell G in same row.

When I run the code, everything gets deleted. Thoughts?

Sub DeleteRows()

Dim strDay, strDayLessOne, strMonth, strYear, strDate1, strDate2 As String

strDay = Format$(Day(Now), "00")
strDayLessOne = Format$(Day(DateAdd("d", -1, Now)), "00")
strMonth = Format$(Month(Now), "00")
strYear = Format$(Year(Now), "####")
strDate1 = "'" + strYear + "-" + strMonth + "-" + strDay + "*"
strDate2 = "'" + strYear + "-" + strMonth + "-" + strDayLessOne + "*"


    Last = Cells(Rows.Count, "A").End(xlUp).Row
    For i = Last To 1 Step -1
        If (Cells(i, "F").Value) = (Cells(i, "G").Value) Then
            Cells(i, "A").EntireRow.Delete
        End If
        If Not (Cells(i, "A").Value) Like strDate1 Then
            If Not (Cells(i, "A").Value) Like strDate2 Then
            Cells(i, "A").EntireRow.Delete
            End If
            Cells(i, "A").EntireRow.Delete
        End If

    Next i

End Sub

 

[SkipVought]

Why not turn on the auto filter, filter columns A & F as required and then DELETE the VISIBLE cells without a loop?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RP1America]

Very interesting, Skip. I'll give that a try.

As always...thanks for your help!!

 

[SkipVought]

        If Cells(i, "A").Value <= Date and Cells(i, "A").Value >= date-1 Then
            Cells(i, "A").EntireRow.Delete
        End If

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

And you should know in your statement:[tt]
Dim strDay, strDayLessOne, strMonth, strYear, strDate1, strDate2 As String
[/tt]
only [tt]strDate2[/tt] is a String, the rest are Variants.

Have fun.

---- Andy

 

[SkipVought]

My question is, why convert any date to a string?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RP1America]

Skip,

Thought I would need to to determine which rows to delete in regards to wanting only to keep today's date and yesterday's date...

The column that I am using contains data like:

'2011-11-02 17:29:54.0
'2011-11-02 17:29:54.0
'2011-11-02 17:29:54.0
'2011-11-02 17:29:54.0
'2011-11-03 03:05:46.0
'2011-11-03 03:05:46.0
'2011-11-03 03:05:46.0
'2011-11-03 03:05:46.0
'2011-11-03 03:05:46.0
'2011-11-03 03:05:46.0

 

[Slovardzen]

Be careful when you use Dim with variables separated by commas. Only the last one will be a string type. The other ones will be Variant type as, by default, if you don't give the variable a type, VBA will do it as variant for you.

So, you can dim them as

Dim Date1 As Date
Date2 As Date
Date3 As Date

Or

Dim Date1 As Date, Date2 As Date and so on.

Did it already work with Skip advices?

 

[SkipVought]

If this is just as copied, then you DO NOT HAVE REAL DATES!!!
[tt]
'2011-11-02 17:29:54.0
'2011-11-02 17:29:54.0
'2011-11-02 17:29:54.0
'2011-11-02 17:29:54.0
'2011-11-03 03:05:46.0
'2011-11-03 03:05:46.0
'2011-11-03 03:05:46.0
'2011-11-03 03:05:46.0
'2011-11-03 03:05:46.0
'2011-11-03 03:05:46.0
[/tt]
the leading APOSTROPHY, indicates that each of these is a STRING.

Also, since you have the time element, the code should be

        If Cells(i, "A").Value < format(Date+1,"yyyy-mm-dd") and Cells(i, "A").Value >= format(date-1,"yyyy-mm-dd") Then
            Cells(i, "A").EntireRow.Delete
        End If

Notice the first expression has LESS THAN Date+1



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

I'd try this:

For i = Last To 1 Step -1
  If Cells(i, "F").Value = Cells(i, "G").Value _
  Or Not (Cells(i, "A").Value Like Format(Date, "yyyy-mm-dd") & "*" _
       Or Cells(i, "A").Value Like Format(Date, "yyyy-mm-dd") & "*") Then
    Cells(i, "A").EntireRow.Delete
  End If
Next i

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[RP1America]

Ended up using a mix of suggestions to get what I needed.

    Last = Cells(Rows.Count, "A").End(xlUp).Row
For i = Last To 1 Step -1
If Cells(i, "A").Value <= DateAdd("d", -2, Date) Then
            Cells(i, "A").EntireRow.Delete
        End If
        If (Cells(i, "F").Value) = (Cells(i, "G").Value) Then
            Cells(i, "A").EntireRow.Delete
        End If

Next i

Thanks for everyone's help!

 

[RP1America]

Just realized I misrepresented my requirements.

Using the following:

    Last = Cells(Rows.Count, "A").End(xlUp).Row
For i = Last To 1 Step -1
If Cells(i, "A").Value <= DateAdd("d", -2, Date) Then
            Cells(i, "A").EntireRow.Delete
        End If
        If (Cells(i, "F").Value) = (Cells(i, "G").Value) Then
            Cells(i, "A").EntireRow.Delete
        End If

Next i

Where the above code is deleting any rows where cell (i, "A") does not equal either today's or yesterday's date. I actually need this to be weekdays only. Therefore, on Monday, I need to include the previous Friday to NOT be deleted. With the code as is, it is only keeping Monday and Sunday (if today is Monday). I need to keep Monday and previous Friday from being deleted.

Thoughts?

 

[SkipVought]

dim iSubtractDays as integer
select case format(YourDate,"ddd")
   case "Mon"
      iSubtractDays = 3
   case else
      iSubtractDays = 1
end select

or whatever values works.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Burser]

That's nice I love simple solutions - that's probably not how I'd have done that. Have a star.

 

[RP1America]

Works wonderfully! Thanks, Skip!

Dim iSubtractDays As Integer
Select Case Format(Date, "ddd")
   Case "Mon"
      iSubtractDays = -4
   Case Else
      iSubtractDays = -2
End Select

    Last = Cells(Rows.Count, "A").End(xlUp).Row
For i = Last To 1 Step -1
If Cells(i, "A").Value <= DateAdd("d", iSubtractDays, Date) Then
            Cells(i, "A").EntireRow.Delete
        End If
        If (Cells(i, "F").Value) = (Cells(i, "G").Value) Then
            Cells(i, "A").EntireRow.Delete
        End If

Next i