VBA Code to Delete Rows with AWR

[brennaboy]

Hi,

In an Excel Spreadsheet I want to do a macro that will delete all rows that contain AWR in Column J.

Do I have to do some sort of loop that will start at the top (or bottom row), check to see if AWR is present and if so delete that row and then move onto the next row until the end of the data is reached.

Or is there an easier way to do the same thing?

Cheers,

Bren.

[Bob Phillips]

[vba]

Dim Lastrow As Long
Dim rng As Range

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "J").End(xlUp).Row
Set rng = .Range("J1").Resize(Lastrow)
rng.AutoFilter field:=1, Criteria1:="AWR"
Set rng = rng.Offset(1, 0)
On Error Resume Next
Set rng = rng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If Not rng Is Nothing Then

rng.EntireRow.Delete
End If

.Range("A1").AutoFilter
End With
[/vba]