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.

 

[Peter_SSs]

.. or better would be to do just one deletion (and this wouldn't matter about the search direction or order).

Sub Group_Billing_v3()
  Dim rFound As Range, rDelete As Range
  Dim firstAddr As String
 
  Set rFound = Cells.Find(What:="Group Billing", LookAt:=xlPart, MatchCase:=False, SearchFormat:=False)
  If Not rFound Is Nothing Then
    Set rDelete = rFound.EntireRow
    firstAddr = rFound.Address
    Do
      Set rFound = Cells.FindNext(After:=rFound)
      Set rDelete = Union(rDelete, rFound.EntireRow)
    Loop Until rFound.Address = firstAddr
    Application.ScreenUpdating = False
    rDelete.Delete
    Application.ScreenUpdating = True
  End If
End Sub

 

[jolivanes]

• You could ditch the Dim line as none of the variables are used

Copy and paste problem

 

[dloskot]

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.

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

Your code work perfectly. Only tried the first If. I wish I better understood what it was doing. I see several other post and solutions but I have not had time to try them all.

 

[jolivanes]

The first "If" statement checks if any cell in the row contains the text you're looking for and deletes the cells in that row from the first column to the last used column up while the second "If" statement would delete the whole row.

Good to hear that all is well again in your excel world and thanks, I am sure also from the other helpers, for letting us know.
Good luck

 

[Peter_SSs]

@dloskot
Glad you have something that is working for you.

As a matter of interest (& because the answers may shed light on other methods - possibly better for your circumstances)

1. What column or columns might the "Group Billing" text occupy?
   
   
2. Your original code in post #1 included LookAt:=xlPart
   That indicated that a cell might include other text as well as "Group Billing" (eg "Group Billing Cycle"), but the post #2 code would not delete such a row. So can you confirm that the "Group Billing" would be the only text in the cell whose row must be deleted?
   
   
3. Can you give a rough estimate of how many of these text values might be found in your sheet (eg just a few or tens or hundreds or thousands)?
   
   
4. Can you give a rough estimate of how many rows your worksheet might contain before the code is run?

 

[jolivanes]

If the concern raised by Peter is indeed a possibility, this should take care of that problem.

Sub Maybe()
Dim lc As Long, lr As Long, i As Long, GB, j As Long
GB = Array("*Group Billing", "Group Billing", "Group Billing*")    '<---- expand to whatever is needed
lc = Cells.Find("*", , , , xlByColumns, xlPrevious).Column
lr = Cells.Find("*", , , , xlByRows, xlPrevious).Row
    For i = lr To 2 Step -1
        For j = LBound(GB) To UBound(GB)
            If Application.CountIf(Rows(i), GB(j)) <> 0 Then Cells(i, 1).Resize(, lc).Delete Shift:=xlUp: Exit For
        Next j
    Next i
End Sub

If the offending text would be in a single column only, autofilter might be the way to go.