Search code examples
excelvba

Delete rows for offsetting values if conditions from others columns were met

I am trying to delete rows for items that offset each other out from column K if additionally conditions from another three columns E, G, H, M, N were met.

enter image description here

!(So If conditions in columns E, G, H, M, N are the same and in column K we have offsetting amounts this row should be deleted. Can anyone please help me?)

Solution

  • This macro can help you:

    Sub DeleteRows()
  Dim i As Long, j As Long, lr As Long, a() As Boolean
  lr = [E2].End(xlDown).Row
  ReDim a(2 To lr) As Boolean
  For i = 2 To lr - 1
    If Not a(i) Then
      For j = i + 1 To lr
        If Not a(j) And Cells(j, "E") = Cells(i, "E") _
            And Cells(j, "G") = Cells(i, "G") _
            And Cells(j, "H") = Cells(i, "H") _
            And Cells(j, "M") = Cells(i, "M") _
            And Cells(j, "N") = Cells(i, "N") _
            And Cells(j, "K") = -Cells(i, "K") _
        Then
          a(i) = True: a(j) = True
          Exit For
        End If
      Next
    End If
  Next
  For i = lr To 2 Step -1
    If a(i) Then Rows(i).Delete
  Next
End Sub

    It assumes there are no empty cells in the column E. Otherwise use any completely filled up column in the next line instead of E:

      lr = [E2].End(xlDown).Row