Hi,

I am working on the following file in excel.


A D
1 -40.07 49085
2 -13,320.00 49086
3 13,320.00 49086
4 -2,000.00 49089
5 -50.00 49091
6 50.00 49091
7 -4,154.40 49092
8 -65.00 49093


I am trying to create a macro that will delete two lines if thet have
the same batch number (column D) and opposite amount in column A.


For example my lines 2 and 3 should be deleted and my lines 5 and 6
should be deleted too.


Thank you for your help


Virginie

Im at work and can't really test it, but this should get you on the
right track...


Sub DeleteRows()

'declare variables
Dim r As Range
Dim rngD As Range

Set rngD = Range("D:D")

For Each r In rngD
'check to see if we have hit the end of
' the list and exit gracefully
If Len(r.Value) = 0 Then Exit For

'See if the current cell value matches
' the cell above's value
If r.Value = ActiveCell.Offset(-1, 0).Value Then
'begin checking to see if the value of
' the cell in the A column have
' similar values
If Abs(ActiveCell.Offset(0, -3).Value) =
Abs(ActiveCell.Offset(-1, -3).Value) Then
'the cells in A have the same or opposite values
ActiveCell.EntireRow.Delete
End If
End If
Next r

End Sub


Hope that helps.

theSquirrel




On Dec 12, 1:04 pm, "Virginie" wrote:
Hi,

I am working on the following file in excel.

A D
1 -40.07 49085
2 -13,320.00 49086
3 13,320.00 49086
4 -2,000.00 49089
5 -50.00 49091
6 50.00 49091
7 -4,154.40 49092
8 -65.00 49093

I am trying to create a macro that will delete two lines if thet have
the same batch number (column D) and opposite amount in column A.

For example my lines 2 and 3 should be deleted and my lines 5 and 6
should be deleted too.

Thank you for your help

Virginie

Public Sub ProcessData()
Const TEST_COLUMN As String = "D" '<=== change to suit
Dim i As Long
Dim iLastRow As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = iLastRow To 2 Step -1
If Cells(i, TEST_COLUMN).Value = Cells(i - 1, TEST_COLUMN).Value
Then
Cells(i, TEST_COLUMN).EntireRow.Delete
End If
Next i

End With

End Sub



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Virginie" wrote in message
ps.com...
Hi,

I am working on the following file in excel.


A D
1 -40.07 49085
2 -13,320.00 49086
3 13,320.00 49086
4 -2,000.00 49089
5 -50.00 49091
6 50.00 49091
7 -4,154.40 49092
8 -65.00 49093


I am trying to create a macro that will delete two lines if thet have
the same batch number (column D) and opposite amount in column A.


For example my lines 2 and 3 should be deleted and my lines 5 and 6
should be deleted too.


Thank you for your help


Virginie

Hi

none of them seam to work (for the 2 scenarios) nothing is happening.
(I do not know much about macros). I do not have any errors (I debugged
a couple) but my excel sheet is not changing.

Could you help?

Virginie

Can you post a workbook somewhere, one of the free web posting facilities?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Virginie" wrote in message
oups.com...
Hi

none of them seam to work (for the 2 scenarios) nothing is happening.
(I do not know much about macros). I do not have any errors (I debugged
a couple) but my excel sheet is not changing.

Could you help?

Virginie

Hi,

the excel file is posted here

www.talmard.com/Virginie/fichier.xls

Virginie

Hi Virginie,

In your example, the data was sorted. In the workbook it wasn't, hence why
it did nothing.

Also, on the duplicate batch numbers, the descriptions are not the same. Is
it okay just to delete the second (third, etc.) that it comes across? If
not, what rule do we apply?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Virginie" wrote in message
oups.com...
Hi,

the excel file is posted here

www.talmard.com/Virginie/fichier.xls

Virginie

I have also found some duplicate batches where the amounts were different.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Virginie" wrote in message
oups.com...
Hi,

the excel file is posted here

www.talmard.com/Virginie/fichier.xls

Virginie