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]

Bump

 

[hmourad]

Bump

 

[hmourad]

Bump

 

[hmourad]

Bump

 

[Peter_SSs]

The code looks familiar.

Try this with a copy of your data to see if it does what you want. I was unsure about row 23. It does not say 'Delete' but it does fit the description given "delete :01 or :31 only under the condition that it is not followed by another :01 or :31"

Sub Del_01_31()
  Dim a As Variant, b As Variant
  Dim nc As Long, i As Long, k As Long
 
  nc = Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
  a = Range("B13", 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
    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
        b(i, 1) = 1
        k = k + 1
      End If
    End If
  Next i
  If k > 0 Then
    Application.ScreenUpdating = False
    With Range("A13").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]

The code looks familiar.

Try this with a copy of your data to see if it does what you want. I was unsure about row 23. It does not say 'Delete' but it does fit the description given "delete :01 or :31 only under the condition that it is not followed by another :01 or :31"

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

  nc = Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
  a = Range("B13", 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
    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
        b(i, 1) = 1
        k = k + 1
      End If
    End If
  Next i
  If k > 0 Then
    Application.ScreenUpdating = False
    With Range("A13").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

Thanks a lot Peter, I’ll give the code a go later today as I still didn’t start my shift, but as for row 23, I need to keep it but if row 22 was alone without 23 then I’d want to delete 22, this is where the tricky part is I think, I should’ve said any row with a :31 or :01 that has :01 or :31 on the row before it, then I don’t want the VBA to remove it, but thanks a lot for the updated code I’ll give it a try asap

 

[Peter_SSs]

So do you want to keep a :01 or :31 if it has a :01 or :31 row before it or after it?
You want to keep both rows 22 and 23?

What to do if a :01 row is immediately followed by a :31 row?
Keep both?
Delete both?
Something else?

BTW, you will generally get faster/better responses in the forum if you provide your sample data with XL2BB so helpers can copy it for testing.

 

[hmourad]

So do you want to keep a :01 or :31 if it has a :01 or :31 row before it or after it?
You want to keep both rows 22 and 23?

What to do if a :01 row is immediately followed by a :31 row?
Keep both?
Delete both?
Something else?

BTW, you will generally get faster/better responses in the forum if you provide your sample data with XL2BB so helpers can copy it for testing.

The code works perfectly, thanks man, you an artist.. As for your question, the data will never have :01 and :31 immediately following each other. So the code you originally created and just updated now works beautifully, again thanks man for the help.

 

[Peter_SSs]

but as for row 23, I need to keep it

The code works perfectly,

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