MS Excel Delete row if criteria is meet

[How Two]

I have a table in the worksheet 'Central'.
This table currently occupies cells B7 to G1000.
The length of the table may vary. So G10001 or more rows.
The data found on first column of the table on column B7 to B????,
there are a series of 'YES' or 'NO' or other text on each cell going
down.

Is it possible for a macro to go through each cell in column B7 to
B???? in the table and delete the entire record row if the criteria
text 'NO' is identified in the cell and remove the formatting and
colors for the other rows which does not have 'NO' as a text.

Can this be done by VBA and without the excel filters?

Appreicate you help in advance...


Many thanks.

 

[Mike H]

Hi,

This goes in a general module and works on the active sheet. You will need
to add to the formatting conditions to remove others I never included.

Sub delete_Me2()
Dim copyrange As Range
lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Set MyRange = Range("B7:B" & lastrow)
For Each c In MyRange
If UCase(c.Value) = "NO" Then
If copyrange Is Nothing Then
Set copyrange = c.EntireRow
Else
Set copyrange = Union(copyrange, c.EntireRow)
End If
End If
If UCase(c.Value) = "YES" Then
With c.EntireRow.Interior
.Pattern = xlNone
End With

With c.EntireRow.Font
.Name = "Arial"
.Size = 11
End With
End If
Next
If Not copyrange Is Nothing Then
copyrange.Delete
End If
End Sub


Mike