DELETE ROWS ACCORDING TO FIGURES

[K]

Hi, i have data in my spreadsheet from coloumn "A" to coloumn "L" . In
coloumn "G" i have codes and in coloumn "J" i have debit and credit
figures. I want macro which should look into coloumn "G" and coloumn
"J" cells and if there are debit and credit figures in coloumn "J"
cells which relates to same code which is in coloumn "G" cells then
Macro should delete those both rows of debit and credit figures from
Range("A:L")
FOR EXAMPLE:

G J ---------coloumns
15SS -100
15SS 100
15SS 200
JK44 300
JK44 -300
JK44 400

Macro should delete 4 rows from above data which will be 15SS -100 and
15SS 100 then also JK44 300 and JK44 -300. i want macro to delete row
from Range("A:L").
Please can anybody help

 

[Don Guillett]

try this
Sub deletecreditdebit()
'On Error Resume Next'probably not needed
For i = Cells(Rows.Count, "g").End(xlUp).Row To 2 Step -1
If Cells(i + 1, "g").Value = Cells(i, "g").Value _
And -Cells(i + 1, "j") = Cells(i, "j") _
And IsNumeric(Cells(i, "j")) Then
Rows(i & ":" & i + 1).delete 'Select
'MsgBox Cells(i, "g")
End If
Next i
End Sub

 

[K]

try this
Sub deletecreditdebit()
  'On Error Resume Next'probably not needed
For i = Cells(Rows.Count, "g").End(xlUp).Row To 2 Step -1
If Cells(i + 1, "g").Value = Cells(i, "g").Value _
 And -Cells(i + 1, "j") = Cells(i, "j") _
 And IsNumeric(Cells(i, "j")) Then
Rows(i & ":" & i + 1).delete   'Select
'MsgBox Cells(i, "g")
End If
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software







- Show quoted text -

Hi Don, Thanks for replying. Your macro works very nice but just want
to tell that i got headings in Row 5 so i want macro to work and start
deleting rows form Row 6 to till down. and other important thing that
some time i dont have credit and debit figures exactly in next cell.
FOR EXAMPLE

G J--------------coloumns
15SS 200
15SS -200
15SS 300
15SS 400
15SS -300

your macro will delet 200 and -200 as they are exactly in next cell
but not deleting the 300 and -300 figure as they are not exactly in
next cell because they have 400 in the middle. can you please let me
know that what should i be doing to cover these two things in your
macro. please note that i cant sort figures in my spreadsheet so i can
have credit and debit figures exactly in next cell as i get data on
daily bases and i need it to be as i feed it in so it easy to work out
in the end.