I need an update to this code (Which I copied from an answer on a post here) that could only delete rows that have :01 and :31.

[hmourad]

Sub Del_Non_00()
  Dim a, b
  Dim nc As Long, i As Long, k As Long
 
  nc = Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, SearchFormat:=False).Column + 1
  a = Range("B1", Range("B" & rows.count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    If Right(Format(a(i, 1), "hh:mm"), 3) <> ":31" Then
      b(i, 1) = 1
      k = k + 1
    End If
  Next i
  If k > 0 Then
    Application.ScreenUpdating = False
    With Range("A1").Resize(UBound(a), nc)
      .Columns(nc).Value = b
      .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
      .Resize(k).EntireRow.Delete
    End With
    Application.ScreenUpdating = True
  End If
 
End Sub

So here instead of "Del_non_00" I basically want it to "Del_01_31", if you know what I mean..

I tried changing
If Right(Format(a(i, 1), "hh:mm"), 3) <> ":31" Then
b(i, 1) = 1
k = k - 1 'changed from + to -
End If
If k > 0 Then changed from < to >
and it just brings these rows to the bottom of the list but doesn't delete them.. and also gives me "Runtime error "1004" (Application -defined or object -defined error) for this code: .Resize(k).EntireRow.Delete

There's also another condition where sometimes I have :01 and :31 values that I need only when the following row is also :01 or :31, so I need to delete :01 or :31 only under the condition that it is not followed by another :01 or :31..

In the attached screenshot it could be clearer..

 

[hmourad]

These two statements are contradictory as my code deletes row 23 from that sample data.

No it doesn’t delete row 23, it keeps both 22 and 23, I tried it and made sure of it.. I’ll double check again tonight.. but for now it works good.

 

[Peter_SSs]

No it doesn’t delete row 23

In that case there is something different about your data and my data.

    If Minute(a(i, 1)) = 1 Or Minute(a(i, 1)) = 31 Then
      If Minute(a(i + 1, 1)) <> 1 And Minute(a(i + 1, 1)) <> 31 Then

This part of my code says
If the minute value in row 23 is 1 or 31 (which it is) then
If the minute part of row 24 is not 1 and it is not 31 (which is true because the minute part of that row is 35) then
delete row 23

Can you post that sample data, or some other small sample data with XL2BB so that I can be sure I am testing with the same data as you? If my code is retaining row 23 in that sample data then it could be treating other rows incorrectly too.

 

[hmourad]

Okay I double checked and it turns out you were right, it does delete 23, I got you some data but different values from the screenshot, I edited it to have some of the conditions that occur, for example a row with :01 followed by another one with :01 and a row with :31 followed by another one with :31 (These are the examples of the ones I don't want removed from the sheet), the condition which I want removed from the sheet automatically is when a row with :31 is neither followed by a :31 nor does it have a previous row with :31, same for :01.

Here's the data you were asking for:

Untitled.csv
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
46	########	19:36:00	UH opened valves no. [14 and 16] and bled-off pressure.	71:06:00	########
47	########	19:01:00		71:09:00	########		31.7	73.2	18.1	2	4	19.1	23.2	38.4
48	########	19:39:00	UH closed valves no. [14 and 16].	71:09:00	########
49	########	19:01:00		71:10:00	########		81.2	73.3	15.9	5	5.6	20.2	23.1	105.6
50	########	19:01:00	UH turned off PORT side burner boom pilot.	71:10:00	########
51	########	19:42:00		71:12:00	########		214.5	73.5	16.8	13	4.8	19.4	23.1	222.6
52	########	19:42:00	Aljassra flushed lines.	71:12:00	########
53	########	19:31:00		71:19:00	########		108.3	73.9	15.2	7	5.1	18	23.4	115.2
54	########	19:31:00	Aljassra closed valve no. [7].	71:19:00	########
55	########	20:00:00		71:30:00	########		90.7	74.1	16.8	5	4.9	18.9	23	97.1
56	########	20:30:00		72:00:00	########		90.2	73.7	16.6	5	4.8	19.9	23.2	96.8
57	########	21:00:00		72:30:00	########		89.5	73.6	15.3	6	5.7	20.1	23.1	96.4
58	########	21:30:00		73:00:00	########		89.5	73.2	16.4	5	4.8	19	23.2	96
59	########	22:00:00		73:30:00	########		89.2	72.6	16.5	5	5.6	19.3	23.4	95.7
60	########	22:01:00	Slickline commenced rig down.	73:30:00	########
61	########	22:01:00		73:31:00	########		89.2	72.8	15.7	6	4.6	20.1	23	95.8
62	########	22:30:00		74:00:00	########		89.2	72.6	15.2	6	4.4	19.7	23.2	95.6
63	########	23:00:00		74:30:00	########		89.2	72.5	15.8	6	4.5	19.9	22.9	95.4
64	########	23:01:00		74:50:00	########		88.8	72.3	16	6	4.5	19.6	23.3	95.5
65	########	23:20:00	Slickline completed rig down.	74:50:00	########
Untitled

 

[Peter_SSs]

Here's the data you were asking for:

Thanks.

It looks to me that for that data, rows 47 and 64 should be the only ones deleted?

Try this version. In the above sample, the first data row is row 46. If that is not the case with your real data, just edit the 'Const' line near the start of the code.

Sub Del_01_31_v2()
  Dim a As Variant, b As Variant
  Dim nc As Long, i As Long, k As Long, m As Long
 
  Const FirstDataRow As Long = 46 '<- Edit if required

  nc = Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
  a = Range("B" & FirstDataRow, Range("B" & Rows.Count).End(xlUp).Offset(1)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a) - 1
    m = Minute(a(i, 1))
    If m = 1 Or m = 31 Then
      If Minute(a(i + 1, 1)) <> m And Minute(a(i - 1, 1)) <> m Then
        b(i, 1) = 1
        k = k + 1
      End If
    End If
  Next i
  If k > 0 Then
    Application.ScreenUpdating = False
    With Range("A" & FirstDataRow).Resize(UBound(a), nc)
      .Columns(nc).Value = b
      .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo
      .Resize(k).EntireRow.Delete
    End With
    Application.ScreenUpdating = True
  End If
End Sub

 

[hmourad]

Yes man

Thanks.

It looks to me that for that data, rows 47 and 64 should be the only ones deleted?

Try this version. In the above sample, the first data row is row 46. If that is not the case with your real data, just edit the 'Const' line near the start of the code.

Sub Del_01_31_v2()
  Dim a As Variant, b As Variant
  Dim nc As Long, i As Long, k As Long, m As Long

  Const FirstDataRow As Long = 46 '<- Edit if required

  nc = Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
  a = Range("B" & FirstDataRow, Range("B" & Rows.Count).End(xlUp).Offset(1)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a) - 1
    m = Minute(a(i, 1))
    If m = 1 Or m = 31 Then
      If Minute(a(i + 1, 1)) <> m And Minute(a(i - 1, 1)) <> m Then
        b(i, 1) = 1
        k = k + 1
      End If
    End If
  Next i
  If k > 0 Then
    Application.ScreenUpdating = False
    With Range("A" & FirstDataRow).Resize(UBound(a), nc)
      .Columns(nc).Value = b
      .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo
      .Resize(k).EntireRow.Delete
    End With
    Application.ScreenUpdating = True
  End If
End Sub

this works very well now, I just adjusted the first row in the code and it works good, really appreciate your help, learning a whole lot! Will definitely recommend this website to anyone asking for Excel help.

 

[Peter_SSs]

Cheers. Glad you have found the forum useful. Thanks for the follow-up.