+ Reply to Thread
Results 1 to 5 of 5

Help with Macro to delete rows with duplicate numbers of opposite signs in the same column

  1. #1
    Registered User
    Join Date
    08-16-2020
    Location
    San Antonio, Texas
    MS-Off Ver
    Office 365
    Posts
    2

    Question 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!
    Attached Files Attached Files

  2. #2
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Help with Macro to delete rows with duplicate numbers of opposite signs in the same co

    Hi pwagner4340

    Welcome to the Forum...Here's one way...
    Please Login or Register  to view this content.
    Last edited by sintek; 08-17-2020 at 01:10 PM.
    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!!!

  3. #3
    Registered User
    Join Date
    08-16-2020
    Location
    San Antonio, Texas
    MS-Off Ver
    Office 365
    Posts
    2

    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?"

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Hi ! Try this VBA demonstration !


    According to the - too light - attachment and the records # as a VBA starter :

    PHP Code: 
    Sub Demo1()
          Const 
    "&""¤""&"
            
    Dim VWD%(), UR&, N&
        
    With Sheet1.UsedRange.Rows("2:" Sheet1.UsedRange.Rows.Count).Resize(, 4).Columns
            V 
    = .Parent.Evaluate(.Item(2).Address & .Item(3).Address)
            
    = .Parent.Evaluate(.Item(2).Address "-" & .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 1 To UBound(W)
                If .
    Exists(W(R1)) Then
                    D
    (R0) = 1:  = .Item(W(R1)) - 1
                    
    If N Then .Item(W(R1)) = Else .Remove W(R1)
                
    End If
            
    Next
               
    .RemoveAll
        End With
               N 
    Application.Sum(D) - 1
            
    If N Then
                Application
    .ScreenUpdating False
               
    .Item(4) = D
               
    .Sort .Item(4), xlAscendingHeader:=xlNo
                Union
    (.Rows(UBound(W) - ":" 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 » !

  5. #5
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    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...
    Attached Files Attached Files
    Last edited by sintek; 08-17-2020 at 03:41 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Delete Duplicate rows with duplicate in 1 column based on value from another column
    By CraigR&M in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-30-2016, 04:36 PM
  2. Delete Duplicate rows with duplicate in 1 column based on value from another column
    By CraigR&M in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-30-2016, 02:06 PM
  3. [SOLVED] Vba to sum up rows based on column a & b and delete duplicate rows
    By kevivu in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-05-2016, 03:15 PM
  4. Delete Duplicate Rows, if column does not contain x
    By JulieQ in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-23-2015, 04:08 PM
  5. Replies: 5
    Last Post: 11-12-2012, 08:38 PM
  6. Replies: 4
    Last Post: 01-25-2011, 08:03 PM
  7. how to delete specific signs or letter in the beginning of a column?
    By wali in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 12-12-2007, 06:22 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1