I can't get this code to run. I didn't mention that there is a header
row. Is this causing the problem since you mentioned VBA being picky
between numeric and text.

I also tried the uppercase lowercase for row S and each way it still
doesn't run.

Perhaps you could help me with this a little further.

Thank you,
Kris

JMB wrote:
1. Double check your data in Column L. Text, numeric, or both? Double check
rng.address in the immediate window while stepping through your code to
ensure SpecialCells is returning the correct range. You could use Union to
merge more than one range.
2. rng is probably is discontiguous range with multiple areas. Try stepping
through the areas first, then the rows in the area.
3. Your offset is not right. In VBA, it is (Row Offset, Column Offset). If
you want column S in the same row use Offset(0, 7).
4. Depending on if "47" could also be numeric, I would coerce to one
particular data type for comparison purposes (using CLng or CStr). VBA
didn't seem to mind, but I hate to depend on it.
5. Depending on what (if any) option compare statements you may be using,
"no" may not be the same as "No". If you use LCase to convert the data to
"no" you should use the same on the other side of your comparison.


Backup before trying.

Sub test()
Dim rng As Range
Dim i As Long, t As Long

On Error Resume Next
Set rng = Sheets("Working").Columns("L").SpecialCells(xlCons tants,
xlTextValues)
If rng Is Nothing Then Exit Sub

On Error GoTo 0
For t = rng.Areas.Count To 1 Step -1
For i = rng.Areas(t).Rows.Count To 1 Step -1
If rng.Areas(t).Cells(i).Value = 47 _
And (rng.Areas(t).Cells(i).Offset(0, 7).Value) = "no" _
Then rng.Areas(t).Cells(i).EntireRow.Delete
Next i
Next t
End Sub

"Kris" wrote:

Hello all and Happy 4th. I need some help if possible. Below is the
code I have found and tried to modify. What I am trying to accomplish
is if "47" is in column "L" AND "No" is in column "S" then that row is
deleted. Here is the code I tried to modify.

Sub Delete_rows_based_on_ColA_ColB()
Sheets("Working").Select
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range, rng As Range, i As Long
Set rng = Columns("L").SpecialCells(xlConstants, xlTextValues)
For i = rng.Count To 1 Step -1
If LCase(rng(i).Value) = "47" _
And LCase(rng(i).Offset(7, 1).Value) = "No" _
Then rng(i).EntireRow.Delete
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Thank you,
Kris