Delete Rows with a specific Date / Time Format

Guzzlr

Well-known Member
Joined
Apr 20, 2009
Messages
759
Code:
'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
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,576
.
Untested here ... I believe this is what you are seeking :

Code:
'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 :

Code:
'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
 
Last edited:

Guzzlr

Well-known Member
Joined
Apr 20, 2009
Messages
759
.
Untested here ... I believe this is what you are seeking :

Code:
'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 :

Code:
'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
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
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...
Code:
[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

Well-known Member
Joined
Apr 20, 2009
Messages
759

ADVERTISEMENT

Does this macro do what you want...
Code:
[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]

Code:
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
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
Code:
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

Well-known Member
Joined
Apr 20, 2009
Messages
759

ADVERTISEMENT

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:
Code:
    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

Well-known Member
Joined
Apr 20, 2009
Messages
759
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
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...
Code:
[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

Well-known Member
Joined
Apr 20, 2009
Messages
759
Try this version then...
Code:
[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:

Code:
  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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,960
Messages
5,525,913
Members
409,671
Latest member
nasseralateek

This Week's Hot Topics

Top