# Delete Rows with a specific Date / Time Format

#### Guzzlr

##### Well-known Member
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

### 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
.
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
.
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
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))
.SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End With
End Sub[/td]
[/tr]
[/table]``````

#### Guzzlr

##### Well-known Member

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))
.SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End With
End Sub[/TD]
[/TR]
</tbody>[/TABLE]``````

Code:
``````With Range("H1", Cells(Rows.Count, "H").End(xlUp))
.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
Code:
``````With Range("H1", Cells(Rows.Count, "H").End(xlUp))
.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

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.

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
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
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))
.SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End With
End Sub[/td]
[/tr]
[/table]``````

#### Guzzlr

##### Well-known Member
Try this version then...
Code:
``````[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub DeleteDateRowsWithTimeValuesInColumnG()
With Range("G1", Cells(Rows.Count, "G").End(xlUp))
.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))
.SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End With``````

Thanks

Last edited:

Replies
3
Views
239
Replies
3
Views
36
Replies
8
Views
110
Replies
2
Views
35
Replies
7
Views
38