VB - How do I delete rows based on a cell values within those rows?

[Jed Shields]

Hi guys,

I'm trying to delete rows that have a specific name in column CL. There are currenly two names and I have found and modified a piece of code but it doesn't work. Any pointers to what I've done wrong? I've rem'd out the code that does work when it looks for just the one name.

Sub DeleteRows()
  
    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    With ActiveSheet

       
        .Select

       
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView

        
        .DisplayPageBreaks = False

        
        Firstrow = .UsedRange.Cells(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

        
        For Lrow = Lastrow To Firstrow Step -1

        
            With .Cells(Lrow, "CL")

                If Not IsError(.Value) Then


                    'If .Value = "Veenod Kurup" Then .EntireRow.Delete   'this works fine when only looking for one name
                    
                    If .Cells(Lrow, "CL").Value = "Veenod Kurup" Or _
                    .Cells(Lrow, "CL").Value = "Robert Pitt" _
                    Then .Rows(Lrow).Delete

                End If
            End With
        Next Lrow
    End With

    ActiveWindow.View = ViewMode
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With
End Sub

Cheers,
Jed

 

[Joe4]

You are already within a "With .Cells..." block, so you do not want "Cells" in your code.
Try mirroring the line of code that you commented out, something like this:

                    If .Value = "Veenod Kurup" Or .Value = "Robert Pitt" Then .EntireRow.Delete

 

[Jed Shields]

Perfect! Nice one :D