Clear contents of range of rows that don't contain certain text values or strings

[spacecaptainsuperguy]

After a bunch of searching on the site here I've found some code (pasted below) that seems to be very close to what I'm trying to accomplish, which is the following:
I have a number of reports that I'm trying to run a macro on to clean them up. At the bottom portion of the report I'm looking to clear the contents of all rows except for two section headings and the row that contains the SUM formulas. Unfortunately not all reports use the exact same language for the section headings but the DO appear to all contain at least the phrases "CURRENT YEAR REPOS" and "PRIOR YEAR PAYOFFS".

The original author commented on their code. I'm looking to alter this code in the following ways, denoted by 2 apostrophes '' and all caps which hopefully makes it easier to distinguish between the two.

Thanks in advance for any assistance.

Code originally found here: Deleting rows if they don't contain certain text values?

Sub DeleteRows()
' Defines variables
Dim Cell As Range, cRange As Range, LastRow As Long, x As Long


' Defines LastRow as the last row of data based on column E
LastRow = ActiveSheet.Cells(Rows.Count, "E").End(xlUp).Row    ''I JUST NEED THE LAST ROW AND ASSUME SOMETHING LIKE THIS WOULD WORK: LastRow = Cells(65536, 1).End(xlUp).Row


' Sets check range as E1 to the last row of E
Set cRange = Range("E1:E" & LastRow)       ''THIS WILL BE CHANGED TO COVER MORE THAN ONE COLUMN...SOMETHING ALONG THE LINES OF:  Range("A" & ReportBottom & ":N" & LastRow)


' For each cell in the check range, working from the bottom upwards
For x = cRange.Cells.Count To 1 Step -1
    With cRange.Cells(x)
        ' If the cell does not contain one of the listed values then...
        If .Value <> "PAID" And .Value <> "PENDING" And .Value <> "CANCELLED" Then   ''I NEED THIS PART TO LOOK ACROSS THE ROWS AND SEE IF ANY PART OF THE CELL CONTAINS THE VALUES OF "CURRENT YEAR REPOS" OR "PRIOR YEAR PAYOFFS" OR "=SUM" AND IF ANY ROW CONTAINS ONE OF THOSE THINGS LEAVE IT BE, IF NOT, CLEAR THE CONTENTS OF THAT ROW.
            ' Delete that row
            .EntireRow.Delete
        End If
    End With
' Check next cell, working upwards
Next x


End Sub

 

[Anfinsen]

i think this is what you're looking for?

Sub DeleteRows()
' Defines variables
Dim Cell As Range, cRange As Range, LastRow As Long, x As Long


' Defines LastRow as the last row of data based on column E
LastRow = ActiveSheet.Cells(Rows.Count, "E").End(xlUp).Row    ''I JUST NEED THE LAST ROW AND ASSUME SOMETHING LIKE THIS WOULD WORK: LastRow = Cells(65536, 1).End(xlUp).Row


' Sets check range as E1 to the last row of E
Set cRange = Range("E1:E" & LastRow)       ''THIS WILL BE CHANGED TO COVER MORE THAN ONE COLUMN...SOMETHING ALONG THE LINES OF:  Range("A" & ReportBottom & ":N" & LastRow)


' For each cell in the check range, working from the bottom upwards
For x = cRange.Cells.Count To 1 Step -1
    With cRange.Cells(x)
        ' If the cell does not contain one of the listed values then...
       If .Value = "CURRENT YEAR REPOS" or .Value = = "PRIOR YEAR PAYOFFS" Then
            .EntireRow.Delete
        End If
    End With
' Check next cell, working upwards
Next x

End Sub

 

[spacecaptainsuperguy]

i think this is what you're looking for?

Sub DeleteRows()
' Defines variables
Dim Cell As Range, cRange As Range, LastRow As Long, x As Long


' Defines LastRow as the last row of data based on column E
LastRow = ActiveSheet.Cells(Rows.Count, "E").End(xlUp).Row    ''I JUST NEED THE LAST ROW AND ASSUME SOMETHING LIKE THIS WOULD WORK: LastRow = Cells(65536, 1).End(xlUp).Row


' Sets check range as E1 to the last row of E
Set cRange = Range("E1:E" & LastRow)       ''THIS WILL BE CHANGED TO COVER MORE THAN ONE COLUMN...SOMETHING ALONG THE LINES OF:  Range("A" & ReportBottom & ":N" & LastRow)


' For each cell in the check range, working from the bottom upwards
For x = cRange.Cells.Count To 1 Step -1
    With cRange.Cells(x)
        ' If the cell does not contain one of the listed values then...
       If .Value = "CURRENT YEAR REPOS" or .Value = = "PRIOR YEAR PAYOFFS" Then
            .EntireRow.Delete
        End If
    End With
' Check next cell, working upwards
Next x

End Sub

Thank you Anfinsen! I'll give this a try.
This line is missing the check for a cell that would include a SUM formula,

If .Value = "CURRENT YEAR REPOS" or .Value = = "PRIOR YEAR PAYOFFS" Then

I'm assuming I can add another or.Value such as: or.Value = "=Sum" without issue?

 

[Anfinsen]

Thank you Anfinsen! I'll give this a try.
This line is missing the check for a cell that would include a SUM formula,

If .Value = "CURRENT YEAR REPOS" or .Value = = "PRIOR YEAR PAYOFFS" Then

I'm assuming I can add another or.Value such as: or.Value = "=Sum" without issue?

That is correct, just add whatever other qualifiers before THEN, and you should be good to go.

 

[spacecaptainsuperguy]

Ok. I think I'm seeing another issue dealing with the range being searched.

The original code was being used in a single column, column E. Mine needs to be multiple columns and rows (I've plugged in some fixed ranges for testing...columns A:N). I believe when the For/Next step is running it is starting at "N & LastRow" and working up column N from there. In this case it is finding none of those values in that column and deleting all the rows. Is there a way to get it to search all cells in a row before moving up to a new row? Also, I need the final step to be Clear Contents rather than Delete. Am I correct that is just simply changing the line .EntireRow.Delete to .EntireRow.ClearContents?

' Sets check range as E1 to the last row of E
Set cRange = Range("A29:N" & LastRow)       ''SET TO A FIXED STARTING POINT FOR TESTING AND ENDING AT ":N" & LastRow)


' For each cell in the check range, working from the bottom upwards
For x = cRange.Cells.Count To 1 Step -1
    With cRange.Cells(x)
        ' If the cell does not contain one of the listed values then...
       If .Value <> "CURRENT YEAR REPOS" Or .Value <> "PRIOR YEAR PAYOFFS" Then   ''CHANGED TO <> SINCE I'M LOOKING TO CLEAR CONTENTS OF ROWS THAT DONT HAVE THIS
            .EntireRow.Delete  ''CAN I JUST CHANGE THIS TO .EntireRow.ClearContents?
        End If
    End With
' Check next cell, working upwards
Next x