Solved: Deleting Rows based on criteria

[DanOfEarth]

Help please,

This is supposed to delete "address rows" on worksheet "Leads" based on a list of zip codes in worksheet "Info"

I'm having trouble on my actual delete line (the red one). Not sure how to reword it:

[vba]Sub DelRows()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CriteriaRng As Range
Dim CalcMode As Long
Dim ViewMode As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With Sheets("Info")
Set CriteriaRng = .Range("B25", .Cells(Rows.Count, "B").End(xlUp))
End With

'Loop through the cells in the Criteria range
For Each Cell In CriteriaRng

With Sheets("Leads")
'Set the first and last row to loop through
Firstrow = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
'Loop from Lastrow to Firstrow (bottom to top)
For Lrow = Lastrow To Firstrow Step -1
'Check the values in the B column
With .Cells(Lrow, "B")
If Not IsError(.Value) Then
If .Value = Range("CriteriaRng") Then .EntireRow.Delete

End If
End With
Next Lrow
End With
Next Cell
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub
[/vba]

Anybody know why.

thank you very much.

[Aussiebear]

You are comparing a value to a range address, so I'm guessing you need to compare a value to a range address value

[DanOfEarth]

"CriteriaRng" is a range filled with the list of values I want it to check

[vba]Set CriteriaRng = .Range("B25", .Cells(Rows.Count, "B").End(xlUp))[/vba]

Now I did try:

[vba]If .Value = CriteriaRng Then .EntireRow.Delete[/vba]

Didn't work. I'm missing something simple.

[Bob Phillips]

Try

[vba]

If Not IsError(Application.Match(.Value, CriteriaRng ,0)) Then .EntireRow.Delete
[/vba]

[GTO]

Greetings Dan,

I have to head out, so no testing, but here is what it looks like to me.

Help please,

This is supposed to delete "address rows" on worksheet "Leads" based on a list of zip codes in worksheet "Info"

I'm having trouble on my actual delete line (the red one). Not sure how to reword it:

I believe your code should jam at:

[vba]If .Value = Range("CriteriaRng") Then .EntireRow.Delete[/vba]

as Range("CriteriaRange") does not exist. The Range Object CriteriaRng exists (No quote marks), but we do not want to try checking one cell's value against an array of values, as that will likewise fail.

You are nearly there, just a slight adjustment. Test against cell.Value from your For Each...

Note that I put a couple of Debug.Print(s) in there. Rid them before moving on, but I wanted to show that these are sometimes helpful. Open up the Immediate Window, and while stepping thru the code (F8) you can see what is happening.

[vba]
Option Explicit

Sub DelRows()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CriteriaRng As Range
Dim CalcMode As Long
Dim ViewMode As Long

Dim cell As Range

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With Sheets("Info")
Set CriteriaRng = .Range("B25", .Cells(Rows.Count, "B").End(xlUp))
End With

'Loop through the cells in the Criteria range
For Each cell In CriteriaRng

With Sheets("Leads")
'Set the first and last row to loop through
Firstrow = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
'Loop from Lastrow to Firstrow (bottom to top)
For Lrow = Lastrow To Firstrow Step -1
'Check the values in the B column
With .Cells(Lrow, "B")

Debug.Print "Cell in Leads I'm checking: " & .Parent.Name & "!" & .Address
If Not IsError(.Value) Then

Debug.Print "Cell I should be checking: " & cell.Parent.Name & "!" & cell.Address

If .Value = cell.Value Then
.EntireRow.Delete
End If

End If
End With
Next Lrow
End With
Next cell

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub[/vba]

BTW, after you are comfortable with this code working, I would suggest looking at Dictionary. That way we could just loop (backwards as you are doing) thru the cells in Leads sheet, and check all the zip codes at once, using .Exists.

Hope that helps,

Mark

[GTO]

... or you could skip Dictionary and do it really smart like Bob there... (LOL)

Gotta go...

[DanOfEarth]

Try

[vba]

If Not IsError(Application.Match(.Value, CriteriaRng ,0)) Then .EntireRow.Delete
[/vba]

THAT WORKED!! Brilliant! I would NEVER have guessed that on my own.

This thing is going to rock.

I can't thank you enough.

And GTO, it looks like your would work as well. I'm reading the explanation now.