VBA Code Error - Delete entire row with given Criteria

[JawaharPrem]

Hi All,

i have to delete entire row with given criteria, i have enclosed sample file for reference.
example - in attached excel file in coloum "type"=if "Vrp and Mnp" the entire rows should be delete.
i have write a code but total data has deleted after running this query. Please help on same.

Sub test()
Dim i As Integer
i = 2
Do While Sheet1.Cells(i, 2) <> ""
i = i + 1
Loop
For i = 1 To 10000
If (Cells(i, 4) = "") Then
Exit For
Else
If ((Cells(i, 4) = "MNP") Or (Cells(i, 4) = "VRP")) Then Rows.EntireRow.Delete
End If
Next i
End Sub


Thanks
Jawahar Prem

 

[AlanSidman]

Try this code:

Option Explicit

Sub RemRow()
    Dim lr As Long, i As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = lr To 2 Step -1
        If Range("D" & i) = "MNP" Or Range("D" & i) = "VRP" Then
            Range("D" & i).EntireRow.Delete
        End If
    Next i
    Application.ScreenUpdating = True
    MsgBox "Action Completed"
End Sub

 

[NARAYANK991]

Hi ,

See the attached file.

Narayan

 

[JawaharPrem]

Hi,
Both Codes are working file thanks to

Narayan & Alansidman.

 

[JawaharPrem]

Try this code:

Option Explicit
 
Sub RemRow()
    Dim lr As Long, i As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = lr To 2 Step -1
        If Range("D" & i) = "MNP" Or Range("D" & i) = "VRP" Then
            Range("D" & i).EntireRow.Delete
        End If
    Next i
    Application.ScreenUpdating = True
    MsgBox "Action Completed"
End Sub

Hi Alansidman,
I modified in my own code its working, please check the code.
thanks..
Sub test()
Dim i As Integer
i = 2
Do While Sheet1.Cells(i, 2) <> ""
i = i + 1
Loop
For i = 1 To 10000
If (Cells(i, 3) = "") Then
Exit For
Else
If ((Cells(i, 4) = "MNP") Or (Cells(i, 4) = "VRP")) Then Cells(i, 4).EntireRow.Delete
End If
Next i
End Sub

 

[AlanSidman]

I'm looking at your code and it appears to me to be inefficient. I do not understand the need for the first loop. What does it do for you? The second loop has a an if statement that if column C is blank then exit the sub other wise go on to do the deletion. Don't understand the rationale of the first part of this loop. Seems overly convoluted if all you really want to do is delete rows. Additionally, when doing a loop to delete rows, one should do it from the bottom to the top to ensure the rows are counted properly. My 2 cents for what its worth.

EDIT: Also, you should use Code Tags for easier reading and alignment when posting VBA code. Read the forum rules on how to make this happen.

 

[JawaharPrem]

Hi Alan,
Thanks for you time, Iam just a begginer in VBA, for my report have work on multiple criteria, hence i used if and loop statment , that is the reason i tried for the same too..

Thanks
Jawahar Prem