Macro to delete rows if a cell in the row = a value

[Will85]

I need a macro that will delete all applicable rows in the spreadsheet if the value in column C from row 5 down = "Yes".

There are breaks/blanks in between my "Yes" values, so it would have to go from the bottom up to determine the last row that could possibly need to be deleted.

I thought I had it, but I ran into an overflow error so I am starting from scratch.

For example, if C5, C52, and C125 = "Yes" then delete rows 5, 52, and 125

Thank you in advance for your help.

 

[Trebor76]

Hi Will85,

Sounds like you have an integer variable but your last row is higher than 32,767 which is the maximum value this variable can handle. See how this goes:

Option Explicit
Option Compare Text 'Allows for non case matching i.e. Yes = yes
Sub Macro1()

    Dim lngLastRow As Long
    Dim lngMyRow As Long
    Dim xlnCalcMethod As XlCalculation
    
    With Application
        .ScreenUpdating = False
        xlnCalcMethod = .Calculation
        .Calculation = xlCalculationManual
    End With

    lngLastRow = Cells(Rows.Count, "C").End(xlUp).Row
    
    For lngMyRow = lngLastRow To 5 Step -1
        If Range("C" & lngMyRow) = "Yes" Then
            Rows(lngMyRow).EntireRow.Delete
        End If
    Next lngMyRow
    
    With Application
        .Calculation = xlnCalcMethod
        .ScreenUpdating = True
    End With

End Sub

Regards,

Robert

 

[Eric W]

Here's another, non-looping, version to try. Test on a copy of your workbook. It assumes you won't have error codes in column C.

Sub DeleteRows()

    On Error Resume Next
    With Range("C5:C" & Cells(Rows.Count, "C").End(xlUp).Row)
        .Replace "YES", "#N/A", xlWhole, , False, , False, False
        .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
    End With
End Sub

 

[Will85]

My "Yes" value is via data validation from a drop down list, is that an issue? Do we need a helper column to identify?

 

[Will85]

This is working, thank you!

 

[Eric W]

Glad we could help!