+ Reply to Thread
Results 1 to 6 of 6

VBA Excel: Identifying a row, then comparing columns in that row and deleting duplicates

Hybrid View

  1. #1
    Registered User
    Join Date
    05-23-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    17

    VBA Excel: Identifying a row, then comparing columns in that row and deleting duplicates

    The aim in mind is to identify a row that has 'US Control Injection'
    Then compare cell A, Cell F, Cell H and Cell O with all other rows.
    Any rows that match all 4 of these cells can then be deleted along with the originally identified row.
    I need to match so many cells as these can differ and i may not want to remove them.

    Attached is an example file (Obviously with sterilised data)
    If the code works properly then I would expect Rows 7,8 and 11,12 to be removed but all others to remain.
    Apologies for not having any code to work from, normally I can adapt other code with my limited knowledge but in this case I don't know where to start.
    Attached Files Attached Files

  2. #2
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    15,038

    Re: VBA Excel: Identifying a row, then comparing columns in that row and deleting duplicat

    Hi MattWardlow

    Try this, although I seem to be having a problem with Your criteria of Column O...I can only assume that the date/times are not exact???
    Option Explicit
    
    Sub removeRows()
    Dim rng As Range, Fnd
    Dim i As Long, ii As Long, lrow As Long
    Dim Crit0 As String, crit1 As String, crit2 As String, crit3 As String, crit4 As String
    Application.ScreenUpdating = False
    With Sheet1
        .AutoFilterMode = False
        lrow = .Cells(Rows.Count, "A").End(xlUp).Row
        For i = 2 To lrow
            Crit0 = "US Control Injection"
            Set Fnd = .Range("E" & i & ":E" & lrow).Find(Crit0, LookIn:=xlValues, lookat:=xlWhole)
            If Not Fnd Is Nothing Then
                ii = Fnd.Row
                crit1 = .Range("A" & ii): crit2 = .Range("F" & ii): crit3 = .Range("H" & ii): crit4 = CStr(.Range("O" & ii))
                Set rng = .Range("A1:V" & lrow)
                With rng
                    .AutoFilter
                    .AutoFilter Field:=1, Criteria1:=crit1
                    .AutoFilter Field:=6, Criteria1:=crit2
                    .AutoFilter Field:=8, Criteria1:=crit3
                    '.AutoFilter Field:=15, Criteria1:=crit4
                    .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
                End With
                .AutoFilterMode = False
                i = ii
                lrow = .Cells(Rows.Count, "A").End(xlUp).Row
            Else
                Exit Sub
            End If
        Next i
    End With
    Application.ScreenUpdating = True
    End Sub
    Last edited by Sintek; 08-24-2017 at 06:22 AM.
    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 [★ Add Reputation] to left of post window...
    Also....Add a comment if you like!!!!
    And remember...Mark Thread as Solved...
    Excel Forum Rocks!!!

  3. #3
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: VBA Excel: Identifying a row, then comparing columns in that row and deleting duplicat

    Try this:-
    Sub MG24Aug38
    Dim Rng As Range, Dn As Range, n As Long, txt As String
    Dim K As Variant, Fd As Boolean, Q As Variant, nRng As Range
    
    Application.ScreenUpdating = False
    Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    
    With CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
    For Each Dn In Rng
        Fd = False
        txt = Join(Application.Index(Dn.Resize(, 15).Value, 0, Array(1, 6, 8, 15)), ",")
        If Dn.Offset(, 4) = "US Control Injection" Then Fd = True
        If Not .Exists(txt) Then
            n = n + 1
            .Add txt, Array(Dn, Fd)
        Else
            Q = .Item(txt)
                If Fd Then Q(1) = True
                Set Q(0) = Union(Q(0), Dn)
           .Item(txt) = Q
    End If
    Next
    For Each K In .keys
        If .Item(K)(1) = True Then
            If nRng Is Nothing Then
                Set nRng = .Item(K)(0)
            Else
                Set nRng = Union(nRng, .Item(K)(0))
            End If
        End If
    Next K
    If Not nRng Is Nothing Then
        nRng.EntireRow.Delete
    End If
    End With
    Application.ScreenUpdating = False
    End Sub
    Regards Mick

  4. #4
    Registered User
    Join Date
    05-23-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    17

    Re: VBA Excel: Identifying a row, then comparing columns in that row and deleting duplicat

    Thanks,

    This seems to do the trick. I will apply to a much larger sheet and go through some verification but looks very promising.

    Thanks again for your help

  5. #5
    Registered User
    Join Date
    05-23-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    17

    Re: VBA Excel: Identifying a row, then comparing columns in that row and deleting duplicat

    Thanks,

    I'm not sure what is playing up column O.
    The great thing this is a code I can apply to different criteria on other spreadsheets should i need to.

  6. #6
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    15,038

    Re: VBA Excel: Identifying a row, then comparing columns in that row and deleting duplicat

    Glad I could be of assistance. thanks for taking time to add to reputation....

+ 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. Replies: 5
    Last Post: 02-11-2016, 04:02 AM
  2. [SOLVED] Comparing 2 columns and deleting duplicates in the second column according first column
    By wingo033 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 11-19-2012, 07:14 PM
  3. Replies: 3
    Last Post: 11-09-2011, 12:36 PM
  4. Identifying Duplicates in 2 columns
    By Excel 2007 Newbie 2 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-15-2011, 10:49 PM
  5. Comparing Lists and Deleting Duplicates
    By Fcroft in forum Excel General
    Replies: 1
    Last Post: 02-17-2010, 02:01 PM
  6. Identifying Duplicates then deleting them and cells either side
    By raehippychick in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-10-2008, 10:51 AM
  7. Identifying Duplicates in Columns
    By DamienO in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-30-2006, 02:45 PM

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