Using find to delete multiple rows

[dloskot]

I have a sheet that I want to find all rows with Group Billing and delete those rows. I have looked online and below is the code I came up with but I keep getting Run Time error 91.

ub Group_Billing()

    Dim Found As Range
    
    Found = Cells.Find(What:="Group Billing", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    If Not Found Is Nothing Then Found.EntireRow.Delete
End Sub

I would appreciate any help.

 

[jolivanes]

Sub Maybe()
Dim lc As Long, lr As Long, i As Long
lc = Cells.Find("*", , , , xlByColumns, xlPrevious).Column
lr = Cells.Find("*", , , , xlByRows, xlPrevious).Row
    For i = lr To 2 Step -1
        If Application.CountIf(Rows(i), "Group Billing") <> 0 Then Cells(i, 1).Resize(, lc).Delete Shift:=xlUp
        'or
        'If Application.CountIf(Rows(i), "Group Billing") <> 0 Then Cells(i, 1).EntireRow.Delete
    Next i
End Sub

 

[Peter_SSs]

You could give this version a try with a copy of your workbook.

Sub Group_Billing_v2()
  Dim Found As Range
 
  Application.ScreenUpdating = False
  Set Found = Cells.Find(What:="Group Billing", LookAt:=xlPart, MatchCase:=False, SearchFormat:=False)
  Do Until Found Is Nothing
    Found.EntireRow.Delete
    Set Found = Cells.Find(What:="Group Billing", LookAt:=xlPart, MatchCase:=False, SearchFormat:=False)
  Loop
  Application.ScreenUpdating = True
End Sub

 

[jolivanes]

We're doling out different possibilities so here is another one.

Sub Maybe_So_Also()
Dim lc As Long, lr As Long, sh3 As Worksheet
With Cells(1, 1).Resize(Cells.Find("*", , , , xlByRows, xlPrevious).Row, Cells.Find("*", , , , xlByColumns, xlPrevious).Column)
    .Replace "Group Billing", "=1/0", xlPart
    .SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
End With
End Sub

Of course it does not use "Find or Found" and if that is a requisite, use Peter's.
As far as speed is concerned, I can't tell you. I am sure that Peter can tell us.

 

[Alex Blakenburg]

Slight mod to Peter's version which should make it more efficient.
Mind you I think using Find is probably the slowest method unless there are only a small number of lines to be deleted.

Sub Group_Billing_Previous()
  Dim Found As Range
  Dim addrFound
 
  Application.ScreenUpdating = False
  Set Found = Cells.Find(What:="Group Billing", LookAt:=xlPart, MatchCase:=False, SearchFormat:=False, SearchDirection:=xlPrevious)
  addrFound = Found.Address
  Do Until Found Is Nothing
    Found.EntireRow.Delete
    Set Found = Cells.FindPrevious(after:=Range(addrFound))
  Loop
  Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

Of course it does not use "Find or Found" ..

A few comments:

• It also assumes (perhaps reasonably but worth mentioning) no existing formula errors on the sheet.
• It did not work for me for cells where "Group Billing" was not the entire text in the cell
• You could ditch the Dim line as none of the variables are used

 

[Peter_SSs]

should make it more efficient.

In what way Alex?

 

[Alex Blakenburg]

In what way Alex?

The original code always (on each find) starts looking from A1 which is the maximum search range and by deleting rows starting from the top it will need to move the maximum number of rows.

 

[Peter_SSs]

The original code always (on each find) starts looking from A1 which is the maximum search range and by deleting rows starting from the top it will need to move the maximum number of rows.

I'm not sure how much difference that would make but if you want to be sure to delete from the bottom up you would need to specify the SearchOrder as xlByRows in your Finds.
That is, unless they are all in the same column which we were not told (& if they are in the same column then Filter would probably be quicker than Find for small data)
As it stands your code found cells for me in this order
H22, F7, D17, A3

 

[Alex Blakenburg]

Oops storing the address should have been inside the loop.
Edited per Peter’s comment

Sub Group_Billing_Previousv2()
  Dim Found As Range
  Dim addrFound
 
  Application.ScreenUpdating = False
  Set Found = Cells.Find(What:="Group Billing", LookAt:=xlPart, MatchCase:=False, SearchFormat:=False, SearchDirection:=xlPrevious, SearchOrder:=xlByRows)

  Do Until Found Is Nothing
    addrFound = Found.Address
    Found.EntireRow.Delete
    Set Found = Cells.FindPrevious(after:=Range(addrFound))
  Loop
  Application.ScreenUpdating = True
End Sub