Need Help with a Fromula or a Macro to Delete Rows with Time 0:00:00 in Cell, Column G

M

mr_ITtoyou

New Member
Joined
Mar 26, 2021
Messages
35
Office Version
  1. 2016
Platform
  1. Windows
I am new to creating formulas or Macros, appreciate any help on a formula or macro that would delete row/s that has a time of 0:00:00 in column G2- G5000.
Thank you

RSA Call Report with Macro to Delete 45 Sec Time_CTRL-X.xlsm
ABCDEFG
1Call Start TimeCall ANICall Routed CSQCall SkillsCall Abandon TimeTime to AbandonTalk Time
28/3/2021 12:298/3/2021 12:38Mammography_CSQ*0:04:36Traci Mizer0:04:03
38/3/2021 12:318/3/2021 12:39Mammography_CSQ*0:07:19Traci Mizer0:00:52
48/3/2021 12:398/3/2021 12:42Radiology_CSQ0:03:430:00:00
58/3/2021 12:488/3/2021 12:57Radiology_CSQ*0:00:02Ingrid Claros0:08:08
68/3/2021 12:498/3/2021 12:59Radiology_CSQ*0:03:45Traci Mizer0:02:34
78/3/2021 12:538/3/2021 13:04Radiology_CSQ*0:04:58Lucinda Diaz0:02:19
88/3/2021 12:538/3/2021 12:56Radiology_CSQ0:02:480:00:00
98/3/2021 12:548/3/2021 13:01Radiology_CSQ*0:04:18Traci Mizer0:01:43
108/3/2021 12:578/3/2021 13:02Radiology_CSQ*0:03:44Traci Mizer0:00:35
118/3/2021 12:598/3/2021 13:07Mammography_CSQ*0:02:32Traci Mizer0:04:08
128/3/2021 13:098/3/2021 13:15Radiology_CSQ*0:00:07Gloria Gutierrez Herrera0:05:21
138/3/2021 13:118/3/2021 13:23Pre_Registration_CSQ*0:06:34Ingrid Claros0:04:30
148/3/2021 13:118/3/2021 13:20Radiology_CSQ*0:06:36Gloria Gutierrez Herrera0:02:15
158/3/2021 13:158/3/2021 13:40Radiology_CSQ*0:04:37Gloria Gutierrez Herrera0:20:01
168/3/2021 13:168/3/2021 13:31Pre_Registration_CSQ*0:05:01Lucinda Diaz0:03:31
178/3/2021 13:178/3/2021 13:35Radiology_CSQ*0:06:03Ingrid Claros0:07:55
188/3/2021 13:198/3/2021 13:39Mammography_CSQ*0:12:20Lucinda Diaz0:04:41
198/3/2021 13:208/3/2021 13:21Radiology_CSQ0:01:150:00:00
208/3/2021 13:218/3/2021 13:22Mammography_CSQ0:01:130:00:00
218/3/2021 13:228/3/2021 13:44Radiology_CSQ*0:17:30Lucinda Diaz0:02:07
228/3/2021 13:238/3/2021 13:38Radiology_CSQ0:14:260:00:00
238/3/2021 13:248/3/2021 13:50Radiology_CSQ*0:21:39Lucinda Diaz0:03:04
248/3/2021 13:288/3/2021 13:53Radiology_CSQ*0:21:30Traci Mizer0:03:22
258/3/2021 13:298/3/2021 13:40Radiology_CSQ0:11:370:00:00
268/3/2021 13:308/3/2021 13:33Radiology_CSQ0:02:510:00:00
278/3/2021 13:338/3/2021 13:47Mammography_CSQ0:13:550:00:00
288/3/2021 13:338/3/2021 13:37Radiology_CSQ0:03:310:00:00
298/3/2021 13:388/3/2021 13:51Mammography_CSQ*0:11:25Lucinda Diaz0:01:08
308/3/2021 13:428/3/2021 13:52Radiology_CSQ0:10:090:00:00
318/3/2021 13:438/3/2021 13:55Radiology_CSQ*0:11:14Gloria Gutierrez Herrera0:01:20
328/3/2021 13:478/3/2021 13:50Physicians_CSQ*0:01:29Traci Mizer0:01:18
338/3/2021 13:478/3/2021 13:49Radiology_CSQ0:01:160:00:00
348/3/2021 13:488/3/2021 13:49Radiology_CSQ0:00:280:00:00
358/3/2021 13:498/3/2021 13:57Radiology_CSQ*0:05:31Traci Mizer0:01:41
368/3/2021 13:508/3/2021 13:52Radiology_CSQ0:01:460:00:00
378/3/2021 13:508/3/2021 13:56Radiology_CSQ*0:05:14Gloria Gutierrez Herrera0:00:45
388/3/2021 13:508/3/2021 13:51Pre_Registration_CSQ0:00:400:00:00
398/3/2021 13:518/3/2021 14:07Mammography_CSQ*0:05:34Gloria Gutierrez Herrera0:07:40
408/3/2021 13:528/3/2021 14:00Pre_Registration_CSQ*0:04:28Traci Mizer0:03:17
418/3/2021 13:528/3/2021 14:08Mammography_CSQ*0:06:43Lucinda Diaz0:03:30
428/3/2021 13:588/3/2021 14:03Physicians_CSQ*0:00:05Ingrid Claros0:04:46
438/3/2021 14:008/3/2021 14:20Radiology_CSQ*0:10:30Traci Mizer0:05:54
448/3/2021 14:008/3/2021 14:20Radiology_CSQ*0:10:30Ingrid Claros0:00:00
458/3/2021 14:038/3/2021 14:16Physicians_CSQ*0:05:22Ingrid Claros0:04:43
468/3/2021 14:058/3/2021 14:40Radiology_CSQ*0:19:17Gloria Gutierrez Herrera0:14:02
478/3/2021 14:118/3/2021 14:36Mammography_CSQ*0:14:27Lucinda Diaz0:03:36
488/3/2021 14:118/3/2021 14:26Pre_Registration_CSQ0:14:260:00:00
498/3/2021 14:148/3/2021 14:25Physicians_CSQ*0:10:17Lucinda Diaz0:00:28
508/3/2021 14:168/3/2021 14:35Radiology_CSQ*0:12:27Traci Mizer0:03:47
518/3/2021 14:228/3/2021 14:38Mammography_CSQ*0:12:18Traci Mizer0:03:28
528/3/2021 14:248/3/2021 14:31Mammography_CSQ0:06:280:00:00
538/3/2021 14:268/3/2021 14:26Radiology_CSQ0:00:100:00:00
548/3/2021 14:298/3/2021 14:40Radiology_CSQ*0:09:57Lucinda Diaz0:00:00
558/3/2021 14:298/3/2021 14:40Radiology_CSQ*0:09:57Lucinda Diaz0:00:51
568/3/2021 14:308/3/2021 14:41Mammography_CSQ*0:09:36Lucinda Diaz0:01:14
578/3/2021 14:328/3/2021 14:36Radiology_CSQ0:03:230:00:00
588/3/2021 14:338/3/2021 14:38Radiology_CSQ0:05:050:00:00
598/3/2021 14:358/3/2021 14:43Mammography_CSQ*0:04:41Traci Mizer0:03:27
608/3/2021 14:398/3/2021 14:50Radiology_CSQ*0:02:35Lucinda Diaz0:04:11
618/3/2021 14:418/3/2021 14:53Radiology_CSQ*0:00:29Ingrid Claros0:09:32
628/3/2021 14:428/3/2021 14:55Radiology_CSQ*0:02:38Gloria Gutierrez Herrera0:06:28
638/3/2021 14:428/3/2021 14:52Mammography_CSQ*0:04:06Traci Mizer0:03:56
648/3/2021 14:448/3/2021 15:02Radiology_CSQ*0:07:14Lucinda Diaz0:04:29
658/3/2021 14:468/3/2021 14:57Mammography_CSQ*0:06:13Traci Mizer0:04:14
668/3/2021 14:488/3/2021 15:14Radiology_Span_CSQ*0:09:23Gloria Gutierrez Herrera0:13:05
678/3/2021 14:488/3/2021 15:11Radiology_CSQ*0:18:05Lucinda Diaz0:02:26
688/3/2021 14:528/3/2021 14:54Physicians_CSQ*0:01:22Ingrid Claros0:00:01
698/3/2021 14:538/3/2021 15:01Physicians_CSQ*0:03:45Traci Mizer0:03:32
708/3/2021 14:558/3/2021 15:10Radiology_CSQ*0:10:43Traci Mizer0:03:56
718/3/2021 14:568/3/2021 15:12Mammography_CSQ*0:14:41Traci Mizer0:00:51
728/3/2021 14:598/3/2021 15:01Radiology_CSQ0:02:210:00:00
738/3/2021 15:008/3/2021 15:03Mammography_CSQ0:02:510:00:00
748/3/2021 15:008/3/2021 15:06Radiology_CSQ0:06:180:00:00
758/3/2021 15:008/3/2021 15:05Pre_Registration_CSQ0:04:240:00:00
768/3/2021 15:028/3/2021 15:23Mammography_CSQ*0:14:07Traci Mizer0:05:46
778/3/2021 15:028/3/2021 15:16Radiology_CSQ*0:09:53Traci Mizer0:04:02
788/3/2021 15:048/3/2021 15:06Physicians_CSQ*0:01:37Traci Mizer0:00:05
798/3/2021 15:048/3/2021 15:18Mammography_CSQ*0:12:33Lucinda Diaz0:00:21
808/3/2021 15:098/3/2021 15:11Physicians_CSQ*0:00:48Traci Mizer0:00:30
818/3/2021 15:138/3/2021 15:17Radiology_CSQ0:04:220:00:00
828/3/2021 15:178/3/2021 15:21Mammography_CSQ*0:00:35Lucinda Diaz0:01:50
838/3/2021 15:188/3/2021 15:38Radiology_CSQ*0:07:03Lucinda Diaz0:05:01
848/3/2021 15:208/3/2021 15:30Radiology_CSQ*0:09:59Ingrid Claros0:00:01
858/3/2021 15:218/3/2021 15:42Mammography_CSQ*0:08:45Ingrid Claros0:09:42
868/3/2021 15:288/3/2021 15:41Radiology_CSQ*0:02:00Traci Mizer0:05:05
878/3/2021 15:308/3/2021 15:48Radiology_CSQ*0:12:02Traci Mizer0:05:03
888/3/2021 15:318/3/2021 15:51Radiology_CSQ*0:11:25Lucinda Diaz0:04:42
898/3/2021 15:328/3/2021 15:50Radiology_CSQ0:18:140:00:00
908/3/2021 15:338/3/2021 15:49Radiology_CSQ0:16:050:00:00
918/3/2021 15:418/3/2021 15:53Mammography_CSQ*0:10:39Lucinda Diaz0:01:27
928/3/2021 15:468/3/2021 15:54Physicians_CSQ*0:04:02Traci Mizer0:04:09
938/3/2021 15:478/3/2021 16:00Radiology_CSQ*0:06:47Ingrid Claros0:04:55
Sheet1
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
How about:

VBA Code: 
Sub DeleteRows()
'
'   Turn Settings off
      Application.ScreenUpdating = False                                            ' Turn Screen Updating off
         Application.Calculation = xlCalculationManual                              ' Turn AutoCalculation off
'
    Dim RowCounter  As Long
'
    For RowCounter = 2 To 5000
        If Range("G" & RowCounter).Value = 0 And Range("G" & RowCounter).Value <> vbNullString Then Rows(RowCounter).Delete
    Next
'
'   Turn Settings back on
         Application.Calculation = xlCalculationAutomatic                           ' Turn AutoCalculation back on
      Application.ScreenUpdating = True                                             ' Turn Screen Updating back on
End Sub
 
Upvote 0
Thanks JohnnyL, not sure why, but not all of the rows deleted with 0:00:00 in the cell in column G.

1631042677152.png



RSA Call Report For The Year-Master Sheet-Don't use Master-Copy Master Sheet.xlsm
ABCDEFG
28/3/2021 12:298/3/2021 12:38Mammography_CSQ*0:04:36Traci Mizer0:04:03
38/3/2021 12:318/3/2021 12:39Mammography_CSQ*0:07:19Traci Mizer0:00:52
48/3/2021 12:488/3/2021 12:57Radiology_CSQ*0:00:02Ingrid Claros0:08:08
58/3/2021 12:498/3/2021 12:59Radiology_CSQ*0:03:45Traci Mizer0:02:34
68/3/2021 12:538/3/2021 13:04Radiology_CSQ*0:04:58Lucinda Diaz0:02:19
78/3/2021 12:548/3/2021 13:01Radiology_CSQ*0:04:18Traci Mizer0:01:43
88/3/2021 12:578/3/2021 13:02Radiology_CSQ*0:03:44Traci Mizer0:00:35
98/3/2021 12:598/3/2021 13:07Mammography_CSQ*0:02:32Traci Mizer0:04:08
108/3/2021 13:098/3/2021 13:15Radiology_CSQ*0:00:07Gloria Gutierrez Herrera0:05:21
118/3/2021 13:118/3/2021 13:23Pre_Registration_CSQ*0:06:34Ingrid Claros0:04:30
128/3/2021 13:118/3/2021 13:20Radiology_CSQ*0:06:36Gloria Gutierrez Herrera0:02:15
138/3/2021 13:158/3/2021 13:40Radiology_CSQ*0:04:37Gloria Gutierrez Herrera0:20:01
148/3/2021 13:168/3/2021 13:31Pre_Registration_CSQ*0:05:01Lucinda Diaz0:03:31
158/3/2021 13:178/3/2021 13:35Radiology_CSQ*0:06:03Ingrid Claros0:07:55
168/3/2021 13:198/3/2021 13:39Mammography_CSQ*0:12:20Lucinda Diaz0:04:41
178/3/2021 13:218/3/2021 13:22Mammography_CSQ0:01:130:00:00
188/3/2021 13:228/3/2021 13:44Radiology_CSQ*0:17:30Lucinda Diaz0:02:07
198/3/2021 13:248/3/2021 13:50Radiology_CSQ*0:21:39Lucinda Diaz0:03:04
208/3/2021 13:288/3/2021 13:53Radiology_CSQ*0:21:30Traci Mizer0:03:22
218/3/2021 13:308/3/2021 13:33Radiology_CSQ0:02:510:00:00
228/3/2021 13:338/3/2021 13:37Radiology_CSQ0:03:310:00:00
238/3/2021 13:388/3/2021 13:51Mammography_CSQ*0:11:25Lucinda Diaz0:01:08
248/3/2021 13:438/3/2021 13:55Radiology_CSQ*0:11:14Gloria Gutierrez Herrera0:01:20
258/3/2021 13:478/3/2021 13:50Physicians_CSQ*0:01:29Traci Mizer0:01:18
268/3/2021 13:488/3/2021 13:49Radiology_CSQ0:00:280:00:00
278/3/2021 13:498/3/2021 13:57Radiology_CSQ*0:05:31Traci Mizer0:01:41
288/3/2021 13:508/3/2021 13:56Radiology_CSQ*0:05:14Gloria Gutierrez Herrera0:00:45
298/3/2021 13:518/3/2021 14:07Mammography_CSQ*0:05:34Gloria Gutierrez Herrera0:07:40
308/3/2021 13:528/3/2021 14:00Pre_Registration_CSQ*0:04:28Traci Mizer0:03:17
318/3/2021 13:528/3/2021 14:08Mammography_CSQ*0:06:43Lucinda Diaz0:03:30
328/3/2021 13:588/3/2021 14:03Physicians_CSQ*0:00:05Ingrid Claros0:04:46
338/3/2021 14:008/3/2021 14:20Radiology_CSQ*0:10:30Traci Mizer0:05:54
348/3/2021 14:038/3/2021 14:16Physicians_CSQ*0:05:22Ingrid Claros0:04:43
358/3/2021 14:058/3/2021 14:40Radiology_CSQ*0:19:17Gloria Gutierrez Herrera0:14:02
368/3/2021 14:118/3/2021 14:36Mammography_CSQ*0:14:27Lucinda Diaz0:03:36
378/3/2021 14:148/3/2021 14:25Physicians_CSQ*0:10:17Lucinda Diaz0:00:28
388/3/2021 14:168/3/2021 14:35Radiology_CSQ*0:12:27Traci Mizer0:03:47
398/3/2021 14:228/3/2021 14:38Mammography_CSQ*0:12:18Traci Mizer0:03:28
408/3/2021 14:268/3/2021 14:26Radiology_CSQ0:00:100:00:00
418/3/2021 14:298/3/2021 14:40Radiology_CSQ*0:09:57Lucinda Diaz0:00:51
428/3/2021 14:308/3/2021 14:41Mammography_CSQ*0:09:36Lucinda Diaz0:01:14
438/3/2021 14:338/3/2021 14:38Radiology_CSQ0:05:050:00:00
448/3/2021 14:358/3/2021 14:43Mammography_CSQ*0:04:41Traci Mizer0:03:27
458/3/2021 14:398/3/2021 14:50Radiology_CSQ*0:02:35Lucinda Diaz0:04:11
468/3/2021 14:418/3/2021 14:53Radiology_CSQ*0:00:29Ingrid Claros0:09:32
478/3/2021 14:428/3/2021 14:55Radiology_CSQ*0:02:38Gloria Gutierrez Herrera0:06:28
RSAWait Time_Agent handled Aug.
 
Upvote 0
Try changing:

VBA Code: 
        If Range("G" & RowCounter).Value = 0 And Range("G" & RowCounter).Value <> vbNullString Then Rows(RowCounter).Delete

to:

VBA Code: 
        If Range("G" & RowCounter).Value < 1.15740740740741E-05 And Range("G" & RowCounter).Value <> vbNullString Then Rows(RowCounter).Delete
 
Upvote 0
When deleting rows one by one, you need to loop from the bottom up, otherwise rows will get missed.
 
Upvote 0
Fluff said:
When deleting rows one by one, you need to loop from the bottom up, otherwise rows will get missed.
Click to expand...
How did I forget that? Sorry, I wasn't thinking. I have a bunch on my mind right now.

VBA Code: 
Sub DeleteRowsV2()
'
'   Turn Settings off
      Application.ScreenUpdating = False                                            ' Turn Screen Updating off
         Application.Calculation = xlCalculationManual                              ' Turn AutoCalculation off
'
    Dim LastRowGcolumn  As Long
    Dim RowCounter  As Long
'
    LastRowGcolumn = Range("G" & Rows.Count).End(xlUp).Row
'
    For RowCounter = LastRowGcolumn To 2 step -1
        If Range("G" & RowCounter).Value = 0 And Range("G" & RowCounter).Value <> vbNullString Then Rows(RowCounter).Delete
    Next
'
'   Turn Settings back on
         Application.Calculation = xlCalculationAutomatic                           ' Turn AutoCalculation back on
      Application.ScreenUpdating = True                                             ' Turn Screen Updating back on
End Sub

Thanks for the kick to the head @Fluff!
 
Upvote 0
Solution
You can also do what you want without using any loops (that way you won't have to remember about looping bottom-up)...
VBA Code: 
Sub DeleteZeroTimes()
  With Range("G1", Cells(Rows.Count, "G").End(xlUp))
    .Replace 0, "#N/A", xlWhole, , , , False, False
    .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  End With
End Sub
 
Upvote 0
johnnyL said:
How did I forget that? Sorry, I wasn't thinking. I have a bunch on my mind right now.

VBA Code: 
Sub DeleteRowsV2()
'
'   Turn Settings off
      Application.ScreenUpdating = False                                            ' Turn Screen Updating off
         Application.Calculation = xlCalculationManual                              ' Turn AutoCalculation off
'
    Dim LastRowGcolumn  As Long
    Dim RowCounter  As Long
'
    LastRowGcolumn = Range("G" & Rows.Count).End(xlUp).Row
'
    For RowCounter = LastRowGcolumn To 2 step -1
        If Range("G" & RowCounter).Value = 0 And Range("G" & RowCounter).Value <> vbNullString Then Rows(RowCounter).Delete
    Next
'
'   Turn Settings back on
         Application.Calculation = xlCalculationAutomatic                           ' Turn AutoCalculation back on
      Application.ScreenUpdating = True                                             ' Turn Screen Updating back on
End Sub

Thanks for the kick to the head @Fluff!
Click to expand...
That worked great. A huge, Thank You for your help on this JohnnyL and Fluff.
 
Upvote 0
Rick Rothstein said:
You can also do what you want without using any loops (that way you won't have to remember about looping bottom-up)...
VBA Code: 
Sub DeleteZeroTimes()
  With Range("G1", Cells(Rows.Count, "G").End(xlUp))
    .Replace 0, "#N/A", xlWhole, , , , False, False
    .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  End With
End Sub
Click to expand...
Rick just saw your post, Thank you for your Macro post too.
 
Upvote 0
You must log in or register to reply here.

Similar threads

BradleyS
  • Solved
Convert Age to Number format not working
Replies
7
Views
109
BradleyS
BradleyS
L
  • Solved
Help Please
2 3
Replies
21
Views
489
Asbestos_Jen
Asbestos_Jen
M
  • Solved
Formula Help.
Replies
3
Views
437
Fluff
Fluff
A
  • Solved
lookupifs?? lookup value so long as date value is greater than specified date
Replies
5
Views
103
Peter_SSs
Peter_SSs
youbitto
  • Solved
Copy paste from multiple Excel and Csv files to another excel file VBA
Replies
2
Views
199
youbitto
youbitto

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top