Delete rows if specific criteria not met.

[Guest]

I have a spreadsheet with 8 columns. I want to delete all rows that don't
have the text "Remittance" in column B. I would like to put the code in an
existing macro. Is this very difficult? Thanks

 

[Chip Pearson]

Try

Dim LastRow As Long
Dim RowNdx As Long
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "B") <> "Remittance" Then
Rows(RowNdx).Delete
End If
Next RowNdx


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


message
news[email protected]...

 

[Guest]

One way:

Sub test()
Const strCriteria As String = "remittance"
Dim rngData As Range
Dim rngCell As Range
Dim rngDelete As Range

With Worksheets("Sheet1") '<<<<<Change
Set rngData = Intersect(.UsedRange, .Columns(2))
End With

For Each rngCell In rngData
If LCase(rngCell.Value) <> LCase(strCriteria) Then
If rngDelete Is Nothing Then
Set rngDelete = rngCell
Else: Set rngDelete = Union(rngDelete, rngCell)
End If
End If
Next rngCell

If Not rngDelete Is Nothing Then _
rngDelete.EntireRow.Delete

End Sub

 

[Barb Reinhardt]

I've done this but don't have the code at home. If you haven't gotten an
answer by tomorrow, I'll copy what I have then.