Delete all rows except...

[joshman]

I receive a weekly report & have been asked to delete all rows except
for two particular rows. Here are the two criteria which are in column
A cells:

AWH98228 and AWL99467

Can anyone offer help?

Thanks

 

[Per Jessen]

Try this:

Sub RemoveRows()
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

LastRow = Range("A" & Rows.Count).End(xlUp).Row
For r = LastRow To 2 Step -1 'Headings in row 1
If Range("A" & r).Value <> "AWH98228" _
And Range("A" & r).Value <> "AWL99467" Then
Rows(r).Delete
End If
Next
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub

Regards,
Per

 

[Mike H]

Hi,

If these 'rows' only appear once then it shouldn't be too onerous to do it
manually so I'm assuming there are many instances. This will delete all rows
except those that have either of thos string in column A

Sub Sonic()
Dim R As Range
Dim V As Variant
Dim S As String
Dim CopyRange As Range
Set Sht = Sheets("Sheet1")'change to suit
lastrow = Sht.Cells(Cells.Rows.Count, "A").End(xlUp).Row
S = "AWH98228,AWL99467"
V = Split(S, ",")
For Each R In Sht.Range("A1:A" & lastrow)
If IsError(Application.Match(CStr(R.Value), V, 0)) Then
If CopyRange Is Nothing Then
Set CopyRange = R.EntireRow
Else
Set CopyRange = Union(CopyRange, R.EntireRow)
End If
End If
Next R

If Not CopyRange Is Nothing Then
CopyRange.Delete
End If
End Sub


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[J_Knowles]

Use Auto Filter

Select all cells with data (in column A) including AWH98228 and AWL99467
values
Turn On Auto Filter
Click on the filter down arrow button
Select Number Filters - Custom Filters

Displays the Custom Filter Input Box
Show rows where:
does not contain AWH98228
and
does not contain AWL99467

click OK

highlight the rows displayed - right click Delete Row

Turn off Auto Filter

and you are left with AWH98228 and AWL99467 rows

HTH,