Solved: Delete row if meet the criteria

[slamet Harto]

Hi there,
Could you please help me on how to delete row based on some of contidions.

I need to delete row if macth of the criteria, for example: in this case I need to delete row
that containing a value starting to 9500 up to 9507 Or 9530 up to 9531 Or 9550 up to 9552

The data in entire column A as follows:
9550-51119551-51129552-51139553-51149554-51159600-11119549-12215200-59485200-59499530-11129531-11119500-4444

I use the following code, but I can't figure out how to meet those criteria
[VBA]
Sub Test()
Dim RngToDel As Range, XRowsCount As Range
Dim r As Long
Sheets(1).Activate
Set RngToDel = ActiveSheet.Range([A1], [A1].End(xlDown))
On Error Resume Next
Application.Calculation = xlCalculationManual
With RngToDel
For r = .Rows.Count To 1 Step -1
If .Cells(r, 1) = "9500" Or .Cells(r, 1) = "9530" Then
.Cells(r, 1).EntireRow.Delete
End If
Next r
End With
Application.Calculation = xlCalculationAutomatic
End Sub[/VBA]


Appreciate your suggestion on this case.
Many thanks, Harto

[f2e4]

Hi Slamet Harto,

This is what I use for deleting rows based on a defined criteria:

See attached file...

It is a document by Ron de Bruin covering the following methods:

Loop backwards through all rows
Loop backwards through all rows and use Union
Use Autofilter to delete rows
Use find to delete rows
Use specialcells to delete rows


The first one on the list works perfectly for me everytime but this should at least give you some variety

It is a very well laid out, simple methodology

[Simon Lloyd]

Slamet it would be better to put your list of numbers to look for in column A on sheet 2 then use the following macro:
[VBA]Sub Find_n_Delete()

Dim Rng As Range, MyCell As Range, Rfound As Range

Set Rng = Sheets("Sheet2").Range("A1:A" & Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row)
For Each MyCell In Rng
With Sheets("Sheet1")
Do
Set Rfound = .Cells.Find(What:=MyCell, After:=.Range("A1"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If Rfound Is Nothing Then GoTo Nxt
Rfound.EntireRow.Delete
Loop Until Rfound Is Nothing
End With
Nxt:
Next MyCell

End Sub[/VBA]

[slamet Harto]

Hi Simon and F2e4

Thank you for your assistance. I've learned more from you all.

Have a great day!
Best,
Slamet harto