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 -
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..