Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need help to update macro to office 2007 macro enabled workbook | Excel Discussion (Misc queries) | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) |