Need vba to delete the rows depends upon criteria of 3

[julielara]

Dear experts

I need your valuable time to my task to accomplish

i have set of data in which b:b column have contains of amount value(plus,minus,zero)
in that i want delete rows which are rows come under certain criteria depends upon country code refelct debit and credit values so it match same value as 0 then minus values rows should be deleted then zero rows should be deleted. keep rest of values which not under these criteria

Like EMAE454 country code twice refelcts under which amount reflect debit/credit balance so it should be delete.

see the attachment

[julielara]

any experts can help me

[Tapion342]

Hello,

well I am not an expert, but maybe this would be some start

Sub Jedziemy_Z_Koksiorem()
Dim a As Long

a = Range("A65000").End(xlUp).Row

'first need to sort this data
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("INPUT").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("INPUT").Sort.SortFields.Add Key:=Range("A2:A" & a), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("INPUT").Sort
        .SetRange Range("A1:D" & a)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

'next check on all data for delete
For Each cell In Range("A2:A" & a)
If cell.Value = cell.Offset(1, 0).Value Then
    If cell.Offset(0, 1).Value + cell.Offset(1, 1).Value = 0 Then cell.Offset(0, 4) = "y"
    
    End If
    
Next cell

For Each cell In Range("A2:A" & a)

If cell.Value = cell.Offset(-1, 0).Value Then
    If cell.Offset(0, 1).Value + cell.Offset(-1, 1).Value = 0 Then cell.Offset(0, 4) = "y"
                
    End If

Next cell
    

For Each cell In Range("B1:B" & a)
If cell.Value <= 0 Then cell.Offset(0, 3) = "y"
Next cell

'now it's delete code
For i = Cells(Rows.Count, "E").End(xlUp).Row To 2 Step -1
    If Cells(i, "E").Value = "y" Then
        Rows(i).Delete shift:=xlUp
    End If
Next

End Sub

[julielara]

How the code get chance if B:B get move to E:E column!!

[Tapion342]

Hello,

I don't know about this new file which u've upload - it's sth else sth new.

I did this macro with the old file and it works - please check on it:

Kopia Deletion.xlsm

Also I told you I am not an expert so I gave u some start only

PS. There is no such a thing as "if B:B get move to E:E column!!", you must copy it wrong m8

[julielara]

Dear sir

Your code works perfectly there is some values not delete which is not come in order plus and minus values


please help on this Find the attachment

[julielara]

sir can u do help on this?