PDA

View Full Version : Solved: Delete the Row based on criteria



slamet Harto
08-08-2008, 10:54 PM
All,

Can you advise me how to delete row if meet criteria.

Many thanks in advance.
Rgds, Harto

Sub DeleteUnprintedList()
Dim RangeToBeDeleted As Range
Dim xRowsCount As Long
Dim r As Long

Sheets(1).Activate
On Error Resume Next
Set RangeToBeDeleted = ActiveSheet.Range([B2], [B2].End(xlDown))


xRowsCount = RangeToBeDeleted.Rows.Count

Application.Calculation = xlCalculationManual

For r = xRowsCount To 1 Step -1

If Left(RangeToBeDeleted(r, 2), 4).Value = "9427" Or "9393" Or "9454" Or "9545" Or "9446" Or "9428" Or "9523" Then
RangeToBeDeleted(r, 2).EntireRow.Delete
End If
Next r

Application.Calculation = xlCalculationAutomatic
End Sub

Bob Phillips
08-09-2008, 02:09 AM
I fear this might be overdoing it, it only left one row of data



Sub DeleteUnprintedList()
Dim RangeToBeDeleted As Range
Dim xRowsCount As Long
Dim r As Long
Dim CheckValue As String

Sheets(1).Activate
On Error Resume Next
Set RangeToBeDeleted = ActiveSheet.Range([B2], [B2].End(xlDown))

' Mendelete rows harus dari bawah, supaya codingnya tidak rumit ..
xRowsCount = RangeToBeDeleted.Rows.Count

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

For r = xRowsCount To 1 Step -1

'Cukup 1 cell (terkiri/kolom 1) saja yg menjadi syarat.. tetapi dengan berbagai kondisi
CheckValue = Left$(RangeToBeDeleted(r, 2).Value, 4)
If CheckValue = "9427" Or CheckValue = "9393" Or CheckValue = "9454" Or _
CheckValue = "9545" Or CheckValue = "9446" Or CheckValue = "9428" Or "CheckValue = 9523" Then

Rows(r).Delete
End If
Next r

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

slamet Harto
08-10-2008, 08:54 PM
Hi Bob,

Thank you for quick response and wonderful code.
you are my master.
Again and againg. Thank you so much.
Best, harto