Results 1 to 2 of 2

Edit existing VBA to remove duplicates and include conditional?

Threaded View

MargyHall Edit existing VBA to remove... 07-25-2013, 06:44 PM
AB33 Re: Edit existing VBA to... 07-26-2013, 06:07 AM
  1. #1
    Registered User
    Join Date
    07-24-2013
    Location
    Pembrokeshire
    MS-Off Ver
    Excel 2007
    Posts
    2

    Edit existing VBA to remove duplicates and include conditional?

    I'm new to VBA and would appreciate help modifying this code. I'm using the code below in the workbook attached. It removes duplicates in Sheet1 and copies values located in Sheet1B&C into Sheet2A&B. The problem is when it copies the data over it creates a ton of duplicates in Sheet2. I need to modify the code to only copy over data from Sheet1B&C to Sheet2A&B if it's not already located in Sheet2 e.g. if the data in Sheet1B3&C3 are already located in Sheet2A&B as a pair in the same row it DOESN'T need to be copied over. I need to eliminate duplicate values in A&B of Sheet2; only unique values should remain.

    I need the data I'm copying from Sheet1 to be copied over as pairs of data in the same row e.g. A2&B2, A3&B3. If duplicates ever appear in Sheet2 they need to be deleted based on whether or not there's data in D and/or C of the same row. If there's data in C and/or D I need the duplicate without data in C and/or D to be deleted.

    I then need to include a conditional format that highlights and row A:D in Sheet2 if C and/or D is blank.

    Sub Macro1()
    Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, rng As Range, c As Range, loc As Range
    Set sh1 = Sheet1
    Set sh2 = Sheet2
    lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row
    sh1.Range("A2:D" & lr).Sort Range("A1"), xlAscending, Range("B1"), , , Range("C3"), xlAscending, Header:=xlNo
        For i = lr To 3 Step -1
            With sh1
                If .Cells(i, 1) = .Cells(i - 1, 1) And .Cells(i, 2) = .Cells(i - 1, 2) _
                    And .Cells(i, 3) = .Cells(i * 1, 3) Then
                        If .Cells(i, 4).Value > .Cells(i - 1, 4).Value Then
                            .Cells(i - 1, 4).EntireRow.Delete
                        Else
                            .Cells(i, 4).EntireRow.Delete
                        End If
                End If
            End With
        Next
    Set rng = Intersect(sh1.Range("B2").Resize(Rows.Count - 2, 1), sh1.UsedRange)
        For Each c In rng
            Set loc = sh2.Range("A2", sh2.Cells(Rows.Count).End(xlUp)).Find(c.Value)
                If Not loc Is Nothing Then
                    If loc.Offset(0, 1) <> c.Offset(0, 1) Then
                        c.Resize(1, 2).Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
                    End If
                Else
                    c.Resize(1, 2).Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
                End If
                
        Next
    End Sub
    0SAMPLE.xlsm
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Conditional Formatting Help, remove duplicates issue
    By drock19 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-04-2012, 05:49 AM
  2. how to include the other column in my existing formulas
    By gerard_gonzales33 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-08-2012, 10:32 AM
  3. [SOLVED] Edit existing macro to include week change
    By adamsurpren in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-19-2012, 08:11 AM
  4. Excel 2007 : Remove Duplicates in excel (conditional)
    By bhanu143143 in forum Excel General
    Replies: 0
    Last Post: 09-14-2011, 02:59 PM
  5. Edit A Line to Include Variations
    By pcargila in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-21-2008, 11:26 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