AMENDMENT IN MACRO
 

MY QUESTION WAS:

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


SOME FRIEND SEND THE MACRO (please see below)

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

MACRO WORKS FINE BUT TWO PROBLEMS (please see below)

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


the above macro will delete 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 please someone
amend the macro above or let me that what should i be doing to cover
these two things which i mentioned above.
Please note that i dont want to sort amount figures in my spreadsheet
as i get data on
daily bases and i need it to be as i feed it in so its easy to work
out in the end. Thats why i cant
have credit and debit figures exactly in next cell some thime.

PLEASE IF ANYBODY CAN HELP!!!!

AMENDMENT IN MACRO
 

Please try this:
Sub deletecreditdebit()
HdgRow = 5
i1 = Cells(Rows.Count, "g").End(xlUp).Row
While i1 = HdgRow + 2
j1 = 1
While i1 - j1 HdgRow And Cells(i1, "g").Value = Cells(i1 - j1,
"g").Value
If -Cells(i1, "j") = Cells(i1 - j1, "j") _
And IsNumeric(Cells(i1, "j")) Then
Rows(i1).Delete
Rows(i1 - j1).Delete
i1 = i1 - 2
j1 = 0
End If
j1 = j1 + 1
Wend
i1 = i1 - 1
Wend
End Sub

It does depend on the data in column G being sorted as in your example.If it
is not then change
While i1 - j1 HdgRow And Cells(i1, "g").Value = Cells(i1 - j1, "g").Value
to
While i1 - j1 HdgRow

and after
If -Cells(i1, "j") = Cells(i1 - j1, "j") _
insert the line
And Cells(i1, "g").Value = Cells(i1 - j1, "g").Value _

It will take longer to loop through the data.

HTH
Gleam

"K" wrote:

MY QUESTION WAS:

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


SOME FRIEND SEND THE MACRO (please see below)

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

MACRO WORKS FINE BUT TWO PROBLEMS (please see below)

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


the above macro will delete 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 please someone
amend the macro above or let me that what should i be doing to cover
these two things which i mentioned above.
Please note that i dont want to sort amount figures in my spreadsheet
as i get data on
daily bases and i need it to be as i feed it in so its easy to work
out in the end. Thats why i cant
have credit and debit figures exactly in next cell some thime.

PLEASE IF ANYBODY CAN HELP!!!!

AMENDMENT IN MACRO
 

On Feb 7, 10:49*pm, Gleam wrote:
Please try this:
Sub deletecreditdebit()
* * HdgRow = 5
* * i1 = Cells(Rows.Count, "g").End(xlUp).Row
* * While i1 = HdgRow + 2
* * * * j1 = 1
* * * * While i1 - j1 HdgRow And Cells(i1, "g").Value = Cells(i1 - j1,
"g").Value
* * * * * * If -Cells(i1, "j") = Cells(i1 - j1, "j") _
* * * * * * And IsNumeric(Cells(i1, "j")) Then
* * * * * * * * Rows(i1).Delete
* * * * * * * * Rows(i1 - j1).Delete
* * * * * * * * i1 = i1 - 2
* * * * * * * * j1 = 0
* * * * * * End If
* * * * * * j1 = j1 + 1
* * * * Wend
* * * * i1 = i1 - 1
* * Wend
End Sub

It does depend on the data in column G being sorted as in your example.If it
is not then change
While i1 - j1 HdgRow And Cells(i1, "g").Value = Cells(i1 - j1, "g").Value
to
While i1 - j1 HdgRow

and after
If -Cells(i1, "j") = Cells(i1 - j1, "j") _
insert the line
And Cells(i1, "g").Value = Cells(i1 - j1, "g").Value _ *

It will take longer to loop through the data.

HTH
Gleam



"K" wrote:
MY QUESTION WAS:

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

SOME FRIEND SEND THE MACRO (please see below)

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

MACRO WORKS FINE BUT TWO PROBLEMS (please see below)

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

the above macro will delete 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 please someone
amend the macro above or let me that what should i be doing to cover
these two things which i mentioned above.
Please note that i dont want to sort amount figures in my spreadsheet
as i get data on
daily bases and i need it to be as i feed it in so its easy to work
out in the end. Thats why i cant
have credit and debit figures exactly in next cell some thime.

PLEASE IF ANYBODY CAN HELP!!!!- Hide quoted text -

- Show quoted text -

Thanks Gleam your Macro working very nice.