Deleting a row when cell's value doesn't match a criteria ?

G

Grek

Hi,

I have some difficulties to delete a row when the value in cell o
column A doesn't match a criteria.

For instance, in A1 the value is 100, A2 is 110, A3 is 120, A4 is 13
and A5 is 140.

I would like to do something like "browse all the values in column
and delete very row with value different that 100 and 120" ---> row A
and A4 should be deleted.

I tried something like that but it didn't work :

***********************************

Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select

Set RangeClass = Range(Selection, Selection.End(xlDown))

For Each Cell In RangeClass
If Cell.Value <> 100 Or 120 Then
Cell.EntireRow.Delete
End If

Next Cell

************************************

Could you please help me ?

Many thanks in advance,

Gregor
 
C

Charles

Grek

Here is something I came up with. There are other way's.
This assumes all info is in column "A" starting at row 1.

Sub delet_row()
Dim rng As Range
Dim i As Integer
Set rng = Worksheets(1).Cells.CurrentRegion
For i = 1 To rng.Rows.Count
If rng(i, 1).Text <> 100 And rng(i, 1).Text <> 120 Then
rng(i, 1).EntireRow.Delete
i = i - 1
End If
If rng(i + 1, 1).Text = "" Then Exit Sub
Next
End Sub

HTH

Charles

Charle
 
Top