macro help
 

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.

macro help
 

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
---------------
Jacob Skaria


"Sasikiran" wrote:

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.

macro help
 

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

--
Gary''s Student - gsnu200856


"Sasikiran" wrote:

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.

macro help
 

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" skrev i meddelelsen
...
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.

macro help
 

Thanks a ton everyone for posting your suggestions.

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Sasikiran" wrote:

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.