Help with Macro to delete rows with duplicate numbers of opposite signs in the same column
Hi,
I'm new to this forum and I would like some help with creating a macro. I am not familiar with macros. The attached file that I am testing has three columns: 1/ Date, 2/ Name, and 3/ Quantity. I would like to delete all rows that has the same person in Column B, and has a corresponding negative and positive amount in column C. For Example, in the attached file, "Phil" has three entries in column B. However, only two of the tree entries would be deleted since there is only one positive amount and a corresponding negative amount in column C. The file that I will eventually use the macro with has over 100,000 records. Could you help me please?
Thanks!
Good Luck
I don't presume to know what I am doing, however, just like you, I too started somewhere...
One-day, One-problem at a time!!!
If you feel I have helped, please click on the star to left of post [Add Reputation]
Also....add a comment if you like!!!!
And remember...Mark Thread as Solved.
Excel Forum Rocks!!!
Re: Help with Macro to delete rows with duplicate numbers of opposite signs in the same co
Thanks for responding Sintek! So I tried the code and I get "Runtime error #438. Object doesn't support this property or method". Did it work when you tested it?"
According to the - too light - attachment and the records # as a VBA starter :
PHP Code:
Sub Demo1() Const S = "&""¤""&" Dim V, W, D%(), U, R&, N& With Sheet1.UsedRange.Rows("2:" & Sheet1.UsedRange.Rows.Count).Resize(, 4).Columns V = .Parent.Evaluate(.Item(2).Address & S & .Item(3).Address) W = .Parent.Evaluate(.Item(2).Address & S & "-" & .Item(3).Address) ReDim D(1 To UBound(W), 0) With CreateObject("Scripting.Dictionary") For Each U In V: .Item(U) = .Item(U) + 1: Next For R = 1 To UBound(W) If .Exists(W(R, 1)) Then D(R, 0) = 1: N = .Item(W(R, 1)) - 1 If N Then .Item(W(R, 1)) = N Else .Remove W(R, 1) End If Next .RemoveAll End With N = Application.Sum(D) - 1 If N Then Application.ScreenUpdating = False .Item(4) = D .Sort .Item(4), xlAscending, Header:=xlNo Union(.Rows(UBound(W) - N & ":" & UBound(W)), .Item(4)).Clear Application.ScreenUpdating = True End If End With End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄
Re: Help with Macro to delete rows with duplicate numbers of opposite signs in the same co
Yes, as per your sample file...
Are you making use of same file?
On what line does it error?
See attached...Copy of sheet and ran code Sheet2 has results...
Bookmarks