Modification needed in VBA

[danelskibr]

Hello,

The code below works fine. The problem is that it is very slow and clunk. I am not knowledgeable enough to find a better wat to make it work more efficiently.

WHAT IT DOES: In an alphabetically sorted column, it rinds each row with the word "Benchmark" and deletes that row. It does this until no Benchmarks remain and then it errors. I have it set to move along once it reaches the error.

WHAT I WANT IT TO DO: Find the first row with the word "Benchmark" in column B. Select down to the last row with the word Benchmark in Column B, and delete these rows.

 Dim StopCell3 As Range
    Set StopCell3 = Columns("B").Find("Benchmark", Cells(Rows.Count, "B"), xlValues, xlWhole, xlNext, True)
    
     On Error Resume Next
    Do
    Set StopCell3 = Columns("B").Find("Benchmark", Cells(Rows.Count, "B"), xlValues, xlWhole, xlNext, True)
   Range(StopCell3, StopCell3.Offset(0, 0)).Select
   Selection.EntireRow.Delete
    Selection.End(xlDown).Offset(1, 0).Select
   Loop While Not StopCell3 Is Nothing
       On Error GoTo 0

Thanks for your help!

 

[steve the fish]

You could do that with an autofilter if your column contains a header.

With ActiveSheet
    If .AutoFilterMode Then .AutoFilterMode = False
    x = .Range("B" & .Rows.Count).End(xlUp).Row
    With .Range("B1").Resize(x)
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:="Benchmark"
        .Range("B1").Offset(1).Resize(x - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
    .AutoFilterMode = False
End With

 

[Rick Rothstein]

If your BENCHMARK cells contain text constants (that is, there are no formulas in those cells), then this code will work quite quickly and, if it is helpful to you at all, there is no need to sort or filter the data beforehand...

Sub DeleteRowWithBENCHMARK()
  Columns("B").Replace "BENCHMARK", "#N/A", xlWhole, , False, False, False
  On Error GoTo NoBenchmarks
  Columns("B").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
NoBenchmarks:
End Sub

 

[danelskibr]

Works perfectly! I was trying to avoid sorting or filtering so this is just what I needed. Thank you very much for your help.