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

New Member
Joined
Jun 12, 2020
Messages
14
Office Version
  1. 2010
Platform
  1. Windows
VBA Code:
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..
 

Attachments

  • 01rows.JPG
    01rows.JPG
    133.1 KB · Views: 20

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

hmourad

New Member
Joined
Jun 12, 2020
Messages
14
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Bump
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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"

VBA Code:
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

New Member
Joined
Jun 12, 2020
Messages
14
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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"

VBA Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 12, 2020
Messages
14
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,536
Messages
5,625,389
Members
416,099
Latest member
sudarsan23

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
Top