VBA Delete rows….

In one of my, worksheets I have one of the fields named “Product”.

If the calculated value is 1, I want to delete that line and the two above it.

If the calculated value is 0, then I want to delete that line only.

How do I do it with VBA?

Thank you

Raghu Prabhu

Top Contributor Asked on April 26, 2017 in Excel.
Add Comment
2 Answer(s)



Here is the solution to resolve your query.

Sub DeleteRows()
 With Sheets("MbrsDetChanged")
    ProductColumn = 1 'Specify Which column has 'Product' as header
    LastRow = 10  'Chnage the last row value
    For i = LastRow To 2 Step -1
       If .Cells(i, ProductColumn) = 0 Then
           .Rows(i).Delete shift:=xlUp
       ElseIf .Cells(i, ProductColumn) = 1 Then
            If i = 2 Then
                .Rows(i).Delete shift:=xlUp
                .Rows(i - 2 & ":" & i).Delete shift:=xlUp
                i = i - 2
            End If
       End If
    Next i
 End With
End Sub


Expert Answered on April 27, 2017.
Add Comment

I don’t know if this is right … the column “Product” is in worksheet “MbrsDetChanged”

comes up with an error

Runtime error 1004

Method range of object _Global fail

Sub DeleteRows()
For i = LastRow To firstrow Step -1
If Range(i, "Product") = 0 Then
Rows(i).Delete shift:=xlUp
ElseIf Range(i, "Product") = 1 Then
Rows(i - 2 & ":" & i).Delete shift:=xlUp
End If
Next i
End Sub


Top Contributor Answered on April 24, 2017.
Add Comment
  • Found this useful?

    Please share using the share button above.

    If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.

    Found the answer useful and wants to credit the user, then vote the answer (vote up).

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.