Deleting rows if they don't contain certain text values?

[BJFDNTN]

Hello,

I've been trying to figure out how to delete entire rows from a spreadsheet that do not contain certain text values. Specifically, the values "PAID" "PENDING" and "CANCELLED". If column E does not contain one of these three text values, then I would like to delete that entire row. How would I be able to achieve this?

Thank you!

 

[Fishboy]

Hello,

I've been trying to figure out how to delete entire rows from a spreadsheet that do not contain certain text values. Specifically, the values "PAID" "PENDING" and "CANCELLED". If column E does not contain one of these three text values, then I would like to delete that entire row. How would I be able to achieve this?

Thank you!

Hi BJFDNTN, welcome to the boards.

One way of doing it would be with a macro like this:

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


' Sets check range as E1 to the last row of E
Set cRange = Range("E1:E" & 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
            ' Delete that row
            .EntireRow.Delete
        End If
    End With
' Check next cell, working upwards
Next x


End Sub

 

[Eric W]

It would be possible to create a macro, but you can do it with a filter just as fast.

Select your column. On the Home Tab, Editing group, click Sort & Filter > Filter. You'll see a down arrow in the column. Click on that. Make sure that the Select All box is checked, then uncheck the PAID, PENDING, and CANCELLED boxes. Click OK. You now see all the rows with different values. Select the rows on the left, right-click and select Delete.

Hope this helps.

 

[jeffreybrown]

Or with a filter macro as Eric describes...

Sub DelRows1()
    Application.ScreenUpdating = False
    With Sheets("Sheet1").Range("E1", Range("E" & Rows.Count).End(xlUp))
        .AutoFilter Field:=1, Criteria1:=Array("Paid", "Pending", "Cancelled"), Operator:=xlFilterValues
        .Offset(1).EntireRow.Delete
        .AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

 

[Fishboy]

Or with a filter macro as Eric describes...

Sub DelRows1()
    Application.ScreenUpdating = False
    With Sheets("Sheet1").Range("E1", Range("E" & Rows.Count).End(xlUp))
        .AutoFilter Field:=1, Criteria1:=Array("Paid", "Pending", "Cancelled"), Operator:=xlFilterValues
        .Offset(1).EntireRow.Delete
        .AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

That is deleting the rows he wants to keep.

 

[jeffreybrown]

Oops, sorry, misread

 

[BJFDNTN]

Hi BJFDNTN, welcome to the boards.

One way of doing it would be with a macro like this:

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


' Sets check range as E1 to the last row of E
Set cRange = Range("E1:E" & 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
            ' Delete that row
            .EntireRow.Delete
        End If
    End With
' Check next cell, working upwards
Next x


End Sub

Thank you, sir! This worked beautifully.

 

[Fishboy]

Thank you, sir! This worked beautifully.

You're most welcome

 

[Rick Rothstein]

Here is another macro that should also work...

[table="width: 500"]
[tr]
	[td]Sub PaidPendingCancelled()
  Dim Col As Long, LastRow As Long
  Col = Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column + 1
  LastRow = Cells(Rows.Count, "E").End(xlUp).Row
  Range(Cells(1, Col), Cells(LastRow, Col)) = Evaluate(Replace("IF(E1:E#=""PAID"","""",IF(E1:E#=""PENDING"","""",IF(E1:E#=""CANCELLED"","""",""X"")))", "#", LastRow))
  Columns(Col).SpecialCells(xlConstants).EntireRow.Delete
End Sub[/td]
[/tr]
[/table]