macro help

  • Thread starter Thread starter Sasikiran
  • Start date Start date
S

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.
 
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
 
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
 
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
 
Back
Top