MACRO TO DELETE ROWS

[K]

ROW A B C G J
5
6 000 G56 TYSS 000G56TYSS 1000
7 001 HJ1 WER5 001HJ1WER5 1900
8 001 HJ1 WER5 001HJ1WER5 -1900
9 546 ST8 SVR5 546ST8SVR5 5600
10 230 W33 ZTT8 230W33ZTT8 -2000
11 230 W33 ZTT8 230W33ZTT8 4500
12 230 W33 ZTT8 230W33ZTT8 2000
13 130 TV5 LK12 130TV5LK12 800
14 152 Y12 RE88 152Y12RE88 9000
15 152 Y12 RE88 152Y12RE88 4400
16 152 Y12 RE88 152Y12RE88 -3000
17 152 Y12 RE88 152Y12RE88 -9000
18 152 Y12 RE88 152Y12RE88 3000
19 003 Q45 OPK2 003Q45OPK2 6000

In Row 5 I have headings and in Range ("A6:C10000") I have data as
show above
in each cell of Range("G6:G10000") I have formula which is "=A6&B6&C6"
and
this formula been drag down to cell "G10000". This formula actually
combines the
value which are given in coloumns "A , B and C". I have figures in
coloumn "J"
I want Macro which should check coloumn "G" and if the values of cells
in coloumn
"G" are same and in same Row of those cell in coloumn "J" cells if I
have "+" and
"-" figures of same amount then Row of those cells from coloumn "A" to
coloumn
"L" should be deleted. For example in above data in coloumn "G" cells
of Row 14 to 18
we have same values of "152Y12RE88" and there are plus and minus
figures in coloumn "J" cells of same Rows of coloumn "G" cell in which
we have same values. The plus
and minus figures next to value of "15Y12RE88" are "9000 , 4400 ,
-3000 , -9000
and 3000". Now macro should delet 9000 and -9000 as they cancel each
other
and these figures are same in amount and also delete 3000 and -3000 so
the
figure should be left now is 4400. Basically I want Row from coloum
"A" to "L"
should be deleted when there are same values in coloumn "G" and there
figures
in coloumn "J" cells next to coloumn "G" cell value which cancel each
other.
Please if any friend can help

(Some time data goes funny when you post message so i cleared the
above data below)

ROW
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

A -------Coloumn

000
001
001
546
230
230
230
130
152
152
152
152
152
003

B------------Coloumn

G56
HJ1
HJ1
ST8
W33
W33
W33
TV5
Y12
Y12
Y12
Y12
Y12
Q45

C---------------Coloumn

TYSS
WER5
WER5
SVR5
ZTT8
ZTT8
ZTT8
LK12
RE88
RE88
RE88
RE88
RE88
OPK2

G---------------Coloumn

000G56TYSS
001HJ1WER5
001HJ1WER5
546ST8SVR5
230W33ZTT8
230W33ZTT8
230W33ZTT8
130TV5LK12
152Y12RE88
152Y12RE88
152Y12RE88
152Y12RE88
152Y12RE88
003Q45OPK2

J----------------Coloumn

1000
1900
-1900
5600
-2000
4500
2000
800
9000
4400
-3000
-9000
3000
6000

 

[Joel]

K: This request is very similar to the following
http://www.microsoft.com/office/com...ming&mid=90648dbd-e9cb-4c79-99d0-b13cb3e30076

Sub DeleteDuplicates1()
'
' Macro1 Macro
' Macro recorded 1/29/2008 by Joel Warburg
'

'
LastRow = Range("A" & Rows.Count).End(xlUp).Row
'sort data
Range("A6:J" & LastRow).Sort _
Key1:=Range("G6"), _
MatchCase:=False, _
Key2:=Range("J6"), _
MatchCase:=False, _

'delete duplicates
'put true in column K if need to be deleted
RowCount = 6
Do While Range("A" & RowCount) <> ""
Match = False
MatchRow = RowCount + 1
Do While Range("G" & RowCount) = Range("G" & MatchRow) And _
Range("J" & RowCount) = abs(Range("J" & MatchRow))

If Range("K" & RowCount) <> True And _
Range("K" & MatchRow) <> True Then

If Range("G" & RowCount) = Range("G" & MatchRow) And _
Range("J" & RowCount) = -1 * Range("J" & MatchRow) Then

Range("K" & RowCount) = True
Range("K" & MatchRow) = True
Exit Do
End If
End If

MatchRow = MatchRow + 1
Loop
RowCount = RowCount + 1
Loop
'Sort by column K which contains True if item should be deleted.
Range("A6:K" & LastRow).Sort _
Key1:=Range("K6")
If Range("K6") = True Then
LastRow = Range("K6").End(xlDown).Row
Rows("6" & ":" & LastRow).Delete
End If

'remove auxillary columns K
Columns("K:K").Delete

end sub

 

[K]

K: This request is very similar to the following:http://www.microsoft.com/office/community/en-us/default.mspx?dg=micro...

Sub DeleteDuplicates1()
'
' Macro1 Macro
' Macro recorded 1/29/2008 by Joel Warburg
'

'
LastRow = Range("A" & Rows.Count).End(xlUp).Row
'sort data
Range("A6:J" & LastRow).Sort _
   Key1:=Range("G6"), _
   MatchCase:=False, _
   Key2:=Range("J6"), _
   MatchCase:=False, _

'delete duplicates
'put true in column K if need to be deleted
RowCount = 6
Do While Range("A" & RowCount) <> ""
   Match = False
   MatchRow = RowCount + 1
   Do While Range("G" & RowCount) = Range("G" & MatchRow) And _
      Range("J" & RowCount) = abs(Range("J" & MatchRow))

      If Range("K" & RowCount) <> True And _
         Range("K" & MatchRow) <> True Then

         If Range("G" & RowCount) = Range("G" & MatchRow) And _
            Range("J" & RowCount) = -1 * Range("J" & MatchRow) Then

            Range("K" & RowCount) = True
            Range("K" & MatchRow) = True
            Exit Do
         End If
      End If

      MatchRow = MatchRow + 1
   Loop
   RowCount = RowCount + 1
Loop
'Sort by column K which contains True if item should be deleted.
Range("A6:K" & LastRow).Sort _
   Key1:=Range("K6")
If Range("K6") = True Then
   LastRow = Range("K6").End(xlDown).Row
   Rows("6" & ":" & LastRow).Delete
End If

'remove auxillary columns K
Columns("K:K").Delete

end sub







- Show quoted text -

Hi Joel, Thanks for replying. I tried putting your macro but its not
working for some reason and also you have put Range "K" and its
deleting coloumn "K" as i havent metioned about coloumn or row K in my
question anywhere. please can you tell me that what should i be doing

 

[Joel]

One line was wrong (see below). My code uses column K as an auxilary column.
This can be change to any empty column.

from
Range("J" & RowCount) = Abs(Range("J" & MatchRow))
to
Abs(Range("J" & RowCount)) = Abs(Range("J" & MatchRow))

 

[K]

One line was wrong (see below).  My code uses column K as an auxilary column.
 This can be change to any empty column.

from
Range("J" & RowCount) = Abs(Range("J" & MatchRow))
to
Abs(Range("J" & RowCount)) = Abs(Range("J" & MatchRow))






- Show quoted text -

Hi Joel, i changed the line which you told me and it did work but
there is still little problem as its not deleting all
the debit and credit figures. the data i have on my spreadsheet is
already been sorted and only need to delete all the debit and credit
figures which can come in same code. Like above in coloum "G" cell
there is one code which is
"152Y12RE88" and with your macro it delete just -3000 and 3000 figures
which come in coloumn "J" cell under the code
i mentioned but macro not deleting the -9000 and 9000 figures which
also relates the same code . Please can you give any help on this.
Thanks

 

[Joel]

There ared a few reasons that code may not completely work

1) the code relies on column A data to determione where the last line of
data is located. if column A doesn't have blnk cells the code will end
before all the data is processed. if this is the case change the test in
column A for another column

Do While Range("A" & RowCount) <> ""

2) The code expects the rows that need to be deleted to be in consecutive
rows. the sort will put the cells in adjacent rows. Make sure the -9000 and
9000 where in consecutive rows before other rows were deleted.

3) There may be extra spaces in the data in columns G and J or zeros where
typed as the letter O. check the data carefully. It doesn't make sense that
only two rows were wrong while the rest of the code worked correctly.