Speed up row deletion

[TheWennerWoman]

Hi,

Is there a quicker way to delete rows than what I'm using:

Sub remove()

Dim lastrow As Long
Dim a As Long
Dim b As Long
Dim c As Long

Application.ScreenUpdating = False
Application.Calculation = xlManual

Sheets("upload").Activate
With ActiveSheet
lastrow = .Cells(Rows.Count, "B").End(xlUp).Row
End With
a = WorksheetFunction.Match("Do Not Post", Range("C1:C" & lastrow), 0)
c = 0
For b = lastrow To a Step -1
Select Case Range("C" & b)
Case "Do Not Post"
Rows(b).Delete
c = c + 1
End Select
Next b
End Sub

Many thanks.

 

[Fluff]

How about

Sub TheWennerWoman()
   With Range("C2", Range("C" & Rows.Count).End(xlUp))
      .Replace "Do not post", True, xlWhole, , False, , False, False
      .SpecialCells(xlConstants, xlLogical).EntireRow.Delete
   End With
End Sub

 

[TheWennerWoman]

Many thanks for that, I'll try that when I'm back in work tomorrow. Has to be quicker than my code.......

 

[Misca]

Try:

Sub RemoveAll()

Dim Rng As Range
Dim FoundRng As Range
Dim TheWord As String

With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With

TheWord = "Do Not Post"

With Sheets("upload")
    Set Rng = .Range("C1:C" & .Cells(Rows.Count, "B").End(xlUp).Row)
    
    Set FoundRng = Find_Range(TheWord, Rng, xlValues, xlWhole)
    
    If Not FoundRng Is Nothing Then
        FoundRng.EntireRow.Delete
    End If
    
End With

With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With

End Sub


Function Find_Range(Find_Item As Variant, _
    Search_Range As Range, _
    Optional LookIn As Variant, _
    Optional LookAt As Variant, _
    Optional MatchCase As Boolean) As Range
     
    Dim c As Range
    Dim FirstAddress As String
    
    If IsMissing(LookIn) Then LookIn = xlValues 'xlFormulas
    If IsMissing(LookAt) Then LookAt = xlPart 'xlWhole
    If IsMissing(MatchCase) Then MatchCase = False
     
    With Search_Range
        Set c = .Find( _
        What:=Find_Item, _
        LookIn:=LookIn, _
        LookAt:=LookAt, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=MatchCase, _
        SearchFormat:=False) 'Delete this term for XL2000 and earlier
        If Not c Is Nothing Then
            Set Find_Range = c
            FirstAddress = c.Address
            Do
                Set Find_Range = Union(Find_Range, c)
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress
        End If
    End With
     
End Function

 

[TheWennerWoman]

How about

Sub TheWennerWoman()
   With Range("C2", Range("C" & Rows.Count).End(xlUp))
      .Replace "Do not post", True, xlWhole, , False, , False, False
      .SpecialCells(xlConstants, xlLogical).EntireRow.Delete
   End With
End Sub

Ok, I've run that but it deletes everything on the sheet

Just for info, "Do Not Post" is actually generated from a formula.......and .Replace appears in my VBA as .replace?

 

[Peter_SSs]

Is there a quicker way to delete rows than what I'm using:

If you have a fairly large range you should find this much quicker. (Don't be put off by the length of the written code. )
I have assumed a header row on 'upload'.

Sub Del_Do_Not_Post()
  Dim a As Variant, b As Variant
  Dim nc As Long, i As Long, k As Long

  With Sheets("upload")
    nc = .Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
    a = .Range("C2", .Range("C" & Rows.Count).End(xlUp)).Value
    ReDim b(1 To UBound(a), 1 To 1)
    For i = 1 To UBound(a)
      If a(i, 1) = "Do Not Post" Then
        b(i, 1) = 1
        k = k + 1
      End If
    Next i
    If k > 0 Then
      Application.ScreenUpdating = False
      With .Range("A2").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 With
End Sub