PDA

View Full Version : Solved: Delete Rows with Condition



fredlo2010
08-02-2012, 07:00 AM
Hello guys,

Here I am again.

I am trying to delete rows in my worksheet that meet a criteria. I want to delete all rows that are empty in column "C" and do not start with "I want" or "Keep" in column "A"
(this is all dummy text)

I have attached an sample of my workbook with two sheets one with the data and the desired output.

8563

Thanks a lot guys

xld
08-02-2012, 07:27 AM
Public Sub ProcessData()
Dim LastRow As Long
Dim i As Long
Dim cell As Range

Application.ScreenUpdating = False

With ActiveSheet

LastRow = .UsedRange.Rows.Count
For i = LastRow To 2 Step -1

If .Cells(i, "C").Value2 = "" And _
Not .Cells(i, "A").Value Like "*I want*" And _
Not .Cells(i, "A").Value Like "*Keep*" Then

.Rows(i).Delete
End If
Next i
End With

Application.ScreenUpdating = True
End Sub

shrivallabha
08-02-2012, 10:24 AM
Here's one more.
Public Sub DelRows()
Dim rng As Range, rngDel As Range
On Error Resume Next
For Each rng In Range("C2:C" & UsedRange.Rows.Count).SpecialCells(4).Offset(, -2)
If Not (rng.Value Like "I want*" Or rng.Value Like "Keep*") Then
If rngDel Is Nothing Then
Set rngDel = rng
Else
Set rngDel = Union(rngDel, rng)
End If
End If
Next rng
On Error GoTo 0
If Not rngDel Is Nothing Then rngDel.EntireRow.Delete
End Sub

fredlo2010
08-04-2012, 06:15 AM
Thanks guys,

Both codes worked. I only have one question to xld.

Is there a reason why you used Value2 and not Value?

Thanks a lot guys