PDA

View Full Version : Solved: Delete row if meet the criteria



slamet Harto
05-14-2008, 02:23 AM
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

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


Appreciate your suggestion on this case.
Many thanks, Harto

f2e4
05-14-2008, 02:46 AM
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
(http://vbaexpress.com/forum/showthread.php?t=19522#Specialcells)

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
05-14-2008, 04:47 AM
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:
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

slamet Harto
05-14-2008, 05:19 AM
Hi Simon and F2e4

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

Have a great day!
Best,
Slamet harto