macro help

[Sasikiran]

Dear,

I need a macro to delete the entire row if the seconds data in the cell is
other than 00.

The data in column B is in h:mm:ss AM/PM format. If the time is other than
00 it displays in the cell. All the rows which has seconds value other than
00 should be deleted.

Thanks in advance.

 

[Jacob Skaria]

Sasikiran

Try the below macro which works on activesheet. Col B. Backup your data and
test.

Sub Macro()
Dim lngRow As Long
Dim lngLastRow As Long
lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
For lngRow = lngLastRow To 1 Step -1
If Second(Range("B" & lngRow)) <> 0 Then Rows(lngRow).Delete
Next
End Sub

If this post helps click Yes

 

[Gary''s Student]

Try:

Sub TimeCleaner()
Dim i As Long, n As Long, v As String, w As String
n = Cells(Rows.Count, "B").End(xlUp).Row
For i = n To 1 Step -1
v = Cells(i, 2).Text
s = Split(v, " ")
If Right(s(0), 2) <> "00" Then
Cells(i, 2).EntireRow.Delete
End If
Next
End Sub


Data like:

2:48:02 AM
1:44:02 AM
8:43:04 AM
3:44:04 AM
5:48:03 AM
6:09:00 AM
4:11:03 AM
7:01:02 AM
9:05:00 AM
3:19:01 AM
5:47:04 AM
10:03:00 AM
4:20:00 AM
6:27:03 AM
2:03:01 AM
2:27:04 AM
1:49:01 AM
9:03:00 AM
10:18:02 AM
5:23:01 AM


will reduce to:

6:09:00 AM
9:05:00 AM
10:03:00 AM
4:20:00 AM
9:03:00 AM

 

[Per Jessen]

Hi

Try this:

Sub AAA()
FirstRow = 2 'Headings in row 1
LastRow = Range("B" & Rows.Count).End(xlUp).Row

For r = LastRow To FirstRow Step -1
If Second(Range("B" & r).Value) <> 0 Then
Rows(r).Delete
End If
Next
End Sub

Regards,
Per

 

[Sasikiran]

Thanks a ton everyone for posting your suggestions.