[xpost][/xpost]
crossposted: https://www.mrexcel.com/board/…d-opposite-signs.1184201/
Good day
Could anyone help with a macro to delete rows, condition have the same reference and two exact numbers but of different signs (opposite).
Thank you in advance for your time 
The attached example should do what you want, based on the information provided.
There is a 2nd copy of Sheet1 - just to enable copying the data back for testing purposes.
That error could be because no cells match the criteria. SpecialCells will produce an error if this occurs so error handling is necessary.
Attach an example workbook not images.
As I indicated in my earlier example, the code worked with the original data provided.
No error handling should be necessary because the SpecialCells in this case was based on the unique values in Col G in the original sample.
The new data you provided however uses Col F instead of G and it includes 'unformatted' data such as "1.46505E+14" which needs special handling.
The change from Col G to Col F and the 'unformatted' values now included in Col F should be able to be handled by the attached revised example. This currently works on the sample provided, there are on guarantees if this data is not representative of the actual data or if, for example, the data format changes.
I would always use error handling with SpecialCells
I would normally use error handling as well however in this specific case it should not be required because the autofiltering was based on an array of unique values derived from the data in the column being filtered
Greeting
The code is running smoothly. It is so precise.
However when it encounters 3 lines with the same references, it wont delete any rows even if 2 of them null each other, but after i remove the one that doesnt has a match and click on the macro again it deletes the pair.
Sometimes I get "overflow runtime error 6" (thats the least of my problems) if I remove some lines (reduce) and click on the macro again, runs fine.
IM VERY HAPPY 
I will even sleep tonight!!
Cant thank you enough mates, that piece of code will help me a lot!
I will just continue to analyze the code, but I guess theres nothing else to be done.
Try this alternative approach, file attached
Code assigned to the button
Option Explicit
Sub DeleteRows()
Dim x, i As Long, ii As Long
With Sheet1.Cells(1).CurrentRegion
x = .Value
For i = 2 To UBound(x, 1)
If Application.CountIf(.Columns(6), x(i, 6)) > 1 Then
ii = i + 1
Do Until ii = UBound(x, 1) + 1
If x(ii, 6) = x(i, 6) And x(ii, 10) + x(i, 10) = 0 Then
x(i, 1) = vbNullString: x(ii, 1) = vbNullString
Exit Do
End If
ii = ii + 1
Loop
End If
Next
.Value = x
On Error Resume Next
.Columns(1).SpecialCells(4).EntireRow.Delete
End With
End SubYou're welcome
Don’t have an account yet? Register yourself now and be a part of our community!