Code to delete row when certain criteria met

[midoop]

Hello Again Forum,

I am trying to delete rows when ever a cell in column G says "No Data" and a cell on the same row in column E is blank.

Below is my code:

lRow = Cells(Rows.Count, "A").End(xlUp).Row
Do While lRow > 0
If Cells(lRow, 7) = "No Data" And Cells(lRow, 5) = "" Then
Rows(lRow).Delete Shift:=xlUp
End If
lRow = lRow - 1
Loop

 

[Jonmo1]

That code works fine for me.

Are you getting an error?

 

[midoop]

No errors. But when I ran the code in debug mode it just skipped all of it.

 

[Jonmo1]

That would mean none of the rows met the criteria.

And the criteria is
The value in G = "No Data" AND The value in E is ""
perhaps you actually mean OR instead of AND


Also, note that VBA is case sensitive.
So if the cell is "no data", then it will not be counted as "No Data"

Try
If Ucase(Cells(lRow, 7)) = "NO DATA" And Cells(lRow, 5) = "" Then

 

[midoop]

I mean AND
And I tried If Ucase(Cells(lRow, 7)) = "NO DATA" And Cells(lRow, 5) = "" Then and I got the same thing.
Then I deleted the And Cells(lRow, 5) = "" part, and same thing. The column is formatted as general and i tried converting it to text and that did not help. It just skipped that part altogether.

 

[midoop]

I got it to work using the code below:

With ws1.Columns("A:I").Resize(ws1.UsedRange.Rows.Count)
.AutoFilter 7, "No Data"
With .Offset(1).SpecialCells(xlCellTypeVisible)
.AutoFilter 5, ""
With .Offset(1).SpecialCells(xlCellTypeVisible)
.EntireRow.Delete
End With
.AutoFilter
End With
.AutoFilter
End With