On Feb 7, 10:49*pm, Gleam <Gl...@discussions.microsoft.com> 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.
|
Similar Threads
