• Ask a Question
150
Insert Image Size must be less than < 5MB.
    Ask a Question
    Cancel
    150
    More answer You can create 5 answer(s).
      Ask a Poll
      Cancel
      Expert

      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

      Asked by raghuprabhu on April 26, 2017 in Excel.
      2 Answers
      Expert

      HI,

       

      Here is the solution to resolve your query.

      Sub DeleteRows()
       
       With Sheets("MbrsDetChanged")
          .Activate
          
          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
                  Else
                      .Rows(i - 2 & ":" & i).Delete shift:=xlUp
                      i = i - 2
                  End If
             End If
          Next i
       End With
      End Sub
      

      Regards!

      Answered by Valli on April 27, 2017..
      Expert

      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()
      Sheets("MbrsDetChanged").Select
      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
      

      Regards
      Raghu

      Answered by raghuprabhu on April 24, 2017..