slamet Harto
06-15-2010, 09:41 PM
HI there
I have around 60.000 of rows data.
I want to delete row if there is a certain value in multiple column.
Please find the following for reference.
Thanks in advance
Sub DEL_Code()
Dim i As Long, lastrow As Long
Dim Start As Double, Finish As Double
Start = Timer
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With ActiveSheet
lastrow = Cells.SpecialCells(xlCellTypeLastCell).Row
End With
For i = lastrow To 1 Step -1
If Cells(i, "L").Value <> "00000000" Then
Cells(i, "L").EntireRow.Delete
If Cells(i, "Q").Value <> "00000000" Then
Cells(i, "Q").EntireRow.Delete
If Cells(i, "O").Value <> "1" Or _
Cells(i, "O").Value <> "2" Then
Cells(i, "O").EntireRow.Delete
If UCase(Cells(i, "M").Value) Like "W*" Or _
Cells(i, "M").Value Like "D*" Or _
Cells(i, "M").Value Like "E*" Or _
Cells(i, "M").Value Like "Q*" Or _
Cells(i, "M").Value = "O3" Or _
Cells(i, "M").Value = "O4" Then
Cells(i, "M").EntireRow.Delete
If UCase(Cells(i, "R").Value) Like "W*" Or _
Cells(i, "R").Value Like "D*" Or _
Cells(i, "R").Value Like "E*" Or _
Cells(i, "R").Value Like "Q*" Or _
Cells(i, "R").Value = "O3" Or _
Cells(i, "R").Value = "O4" Then
Cells(i, "R").EntireRow.Delete
End If
Next i
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
Finish = Timer
MsgBox "Delete Time: " & Finish - Start & " Second"
End Sub
I have around 60.000 of rows data.
I want to delete row if there is a certain value in multiple column.
Please find the following for reference.
Thanks in advance
Sub DEL_Code()
Dim i As Long, lastrow As Long
Dim Start As Double, Finish As Double
Start = Timer
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With ActiveSheet
lastrow = Cells.SpecialCells(xlCellTypeLastCell).Row
End With
For i = lastrow To 1 Step -1
If Cells(i, "L").Value <> "00000000" Then
Cells(i, "L").EntireRow.Delete
If Cells(i, "Q").Value <> "00000000" Then
Cells(i, "Q").EntireRow.Delete
If Cells(i, "O").Value <> "1" Or _
Cells(i, "O").Value <> "2" Then
Cells(i, "O").EntireRow.Delete
If UCase(Cells(i, "M").Value) Like "W*" Or _
Cells(i, "M").Value Like "D*" Or _
Cells(i, "M").Value Like "E*" Or _
Cells(i, "M").Value Like "Q*" Or _
Cells(i, "M").Value = "O3" Or _
Cells(i, "M").Value = "O4" Then
Cells(i, "M").EntireRow.Delete
If UCase(Cells(i, "R").Value) Like "W*" Or _
Cells(i, "R").Value Like "D*" Or _
Cells(i, "R").Value Like "E*" Or _
Cells(i, "R").Value Like "Q*" Or _
Cells(i, "R").Value = "O3" Or _
Cells(i, "R").Value = "O4" Then
Cells(i, "R").EntireRow.Delete
End If
Next i
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
Finish = Timer
MsgBox "Delete Time: " & Finish - Start & " Second"
End Sub