Delete Rows with a specific Date / Time Format

[Guzzlr]

'Step Through Rows Backwards and Delete Matching Data
    For MyRow = LastRow To 3 Step -1
        If (Cells(MyRow, "K") = Cells(MyRow - 1, "K")) Then
            Rows(MyRow - 1 & ":" & MyRow).Delete
        End If
    Next MyRow

Hello All, the above code is working good for me to find and delete the rows with matching numbers.
I want to shift gears and delete rows that Do Not have the following Date / Time format in Column G:

mm/dd/yyyy 00:00:00

If a row has a Date / Time format in coloumn G other than the above, that row would be deleted.
I'm not having luck trying to get the code to work based on my loop example above

Thanks for the help

 

[Logit]

.
Untested here ... I believe this is what you are seeking :

'Step Through Rows Backwards and Delete Matching Data
    For MyRow = LastRow To 3 Step -1
        If (Cells(MyRow, "G") <> Cells(MyRow - 1, "K")) Then
            Rows(MyRow - 1 & ":" & MyRow).Delete
        End If
    Next MyRow

Or maybe :

'Step Through Rows Backwards and Delete Matching Data
    For MyRow = LastRow To 3 Step -1
        If Not (Cells(MyRow, "G") <> Cells(MyRow - 1, "K")) Then
            Rows(MyRow - 1 & ":" & MyRow).Delete
        End If
    Next MyRow

 

[Guzzlr]

.
Untested here ... I believe this is what you are seeking :

'Step Through Rows Backwards and Delete Matching Data
    For MyRow = LastRow To 3 Step -1
        If (Cells(MyRow, "G") <> Cells(MyRow - 1, "K")) Then
            Rows(MyRow - 1 & ":" & MyRow).Delete
        End If
    Next MyRow

Or maybe :

'Step Through Rows Backwards and Delete Matching Data
    For MyRow = LastRow To 3 Step -1
        If Not (Cells(MyRow, "G") <> Cells(MyRow - 1, "K")) Then
            Rows(MyRow - 1 & ":" & MyRow).Delete
        End If
    Next MyRow

I don't believe this will work, because the Date/time stamps are different. What I'm trying to key in on, is a time of 00:00:00 (hh:mm:ss). If this time is all zero's. then that row is saved, and all others are deleted.
Thanks

 

[Rick Rothstein]

I don't believe this will work, because the Date/time stamps are different. What I'm trying to key in on, is a time of 00:00:00 (hh:mm:ss). If this time is all zero's. then that row is saved, and all others are deleted.

Does this macro do what you want...

[table="width: 500"]
[tr]
	[td]Sub DeleteDateRowsWithTimeValuesInColumnG()
  With Range("G1", Cells(Rows.Count, "G").End(xlUp))
    .Value = Evaluate(Replace("IF(@<>INT(@),""#N/A"",IF(@="""","""",@))", "@", .Address))
    .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  End With
End Sub[/td]
[/tr]
[/table]

 

[Guzzlr]

Does this macro do what you want...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub DeleteDateRowsWithTimeValuesInColumnG()
  With Range("G1", Cells(Rows.Count, "G").End(xlUp))
    .Value = Evaluate(Replace("IF(@<>INT(@),""#N/A"",IF(@="""","""",@))", "@", .Address))
    .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  End With
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

With Range("H1", Cells(Rows.Count, "H").End(xlUp))
    .Value = Evaluate(Replace("IF(@<>INT(@),""#N/A"",IF(@="""","""",@))", "@", .Address))
    .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End With

I have your code (Column G replaced with H). The above code deleted all rows.
an example of the data in column H:

03/01/2019 12:41:33

Because the time of 12:41:33 is not 00:00:00, then that row would be deleted.
Any row with a time of: 00:00:00 would be saved

Thanks for the help

 

[Rick Rothstein]

With Range("H1", Cells(Rows.Count, "H").End(xlUp))
    .Value = Evaluate(Replace("IF(@<>INT(@),""#N/A"",IF(@="""","""",@))", "@", .Address))
    .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End With

I have your code (Column G replaced with H). The above code deleted all rows.
an example of the data in column H:

03/01/2019 12:41:33

Why did you change the column reference from G to H when you said "I want to shift gears and delete rows that Do Not have the following Date / Time format in Column G:"?

Are your date/time values real Excel dates or just text values that look like date/time values?

 

[Guzzlr]

Why did you change the column reference from G to H when you said "I want to shift gears and delete rows that Do Not have the following Date / Time format in Column G:"?

Are your date/time values real Excel dates or just text values that look like date/time values?

changed from G to H because the template where I paste the data starts in cell B3, which shifted columns to the right. as for as the Date/time format, this is not text to my knowledge.

What about something like:

    For MyRow = LastRow To 3 Step -1
        If (Cells(MyRow, "H") <> ("mm:dd:yyyy 00:00:00")Then
            Rows(MyRow - 1 & ":" & MyRow).Delete
        End If
    Next MyRow

 

[Guzzlr]

So I'm re-reading my original post. I made a mistake. If the Time Is 00:00:00, then that row would be deleted. I had it backwards.
Sorry for the confusion.

 

[Rick Rothstein]

So I'm re-reading my original post. I made a mistake. If the Time Is 00:00:00, then that row would be deleted. I had it backwards.
Sorry for the confusion.

Try this version then...

[table="width: 500"]
[tr]
	[td]Sub DeleteDateRowsWithTimeValuesInColumnG()
  With Range("G1", Cells(Rows.Count, "G").End(xlUp))
    .Value = Evaluate(Replace("IF(@=INT(@),""#N/A"",IF(@="""","""",@))", "@", .Address))
    .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  End With
End Sub[/td]
[/tr]
[/table]

 

[Guzzlr]

Try this version then...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub DeleteDateRowsWithTimeValuesInColumnG()
  With Range("G1", Cells(Rows.Count, "G").End(xlUp))
    .Value = Evaluate(Replace("IF(@=INT(@),""#N/A"",IF(@="""","""",@))", "@", .Address))
    .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  End With
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

I tried this, only replacing the G with H. So G1 became H1 and G became H. The program left all the 00:00:00 times and deleted the header. In other words, the data is pasted into cell B3, which is the header. The header is in row 3 beginning in B3. all the data is in B4:AC7400 (approximate). So the header is in B3:AC3.
I would like to delete all the rows with a time stamp of 00:00:00. Any other time stamp, would remain.

Example:

04/25/2019 00:00:00 would be deleted
04/25/2019 17:25:14 would not be deleted



Code I used:

  With Range("H1", Cells(Rows.Count, "H").End(xlUp))
    .Value = Evaluate(Replace("IF(@=INT(@),""#N/A"",IF(@="""","""",@))", "@", .Address))
    .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  End With

Thanks