Results 1 to 6 of 6

Macro is just not doing what I want it to as in filtering

Threaded View

  1. #1
    Forum Contributor
    Join Date
    08-02-2005
    Location
    LONDON
    MS-Off Ver
    OFFICE365
    Posts
    168

    Macro is just not doing what I want it to as in filtering

    Hi

    I have a macro that deletes matching entries but it dont seem to be doing what I want it to and I have found out if I filter by individual machines then it works.

    The macro I have at the moment is shown below. Also attached is the workbook that its in.

    What im trying to do is set it up so it filters it out by machine before the macro runs and then moves onto next machine and carry's on until it gets to and runs the last machine. The machine number will always be in column A of both sheets

    Any ideas?

    Option Explicit

     Sub DeleteIdenticalRecordsFromTwoSheets()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim lr1 As Long, lr2 As Long, i As Long
    Dim x, y, xx(), yy(), dict1, dict2
    Dim delRng1 As Range, delRng2 As Range
    
    Application.ScreenUpdating = False
    
    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")
    
    lr1 = ws1.Cells(Rows.Count, 1).End(xlUp).Row
    lr2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row
    
    ws1.Range("A1:D" & lr1).RemoveDuplicates Columns:=Array(1, 2, 3, 4), Header:=xlNo
    
    lr1 = ws1.Cells(Rows.Count, 1).End(xlUp).Row
    x = ws1.Range("A1:D" & lr1).Value
    y = ws2.Range("A1:D" & lr2).Value
    
    Set dict1 = CreateObject("Scripting.Dictionary")
    Set dict2 = CreateObject("Scripting.Dictionary")
    
    For i = 1 To UBound(x, 1)
        dict1.Item(x(i, 1) & x(i, 2) & x(i, 3) & x(i, 4)) = ws1.Range("A" & i).Address
    Next i
    
    For i = 1 To UBound(y, 1)
        dict2.Item(y(i, 1) & y(i, 2) & y(i, 3) & y(i, 4)) = ws2.Range("A" & i).Address
    Next i
    
    ws1.Columns("E").Clear
    ws2.Columns("E").Clear
    
    For i = 1 To UBound(x, 1)
        If dict2.exists(x(i, 1) & x(i, 2) & x(i, 3) & x(i, 4)) Then
            If delRng1 Is Nothing Then
                Set delRng1 = ws1.Range(dict1.Item(x(i, 1) & x(i, 2) & x(i, 3) & x(i, 4)))
            Else
                Set delRng1 = Union(delRng1, ws1.Range(dict1.Item(x(i, 1) & x(i, 2) & x(i, 3) & x(i, 4))))
            End If
        Else
            ws1.Cells(i, 5) = "Missing"
        End If
    Next i
    
    For i = 1 To UBound(y, 1)
        If dict1.exists(y(i, 1) & y(i, 2) & y(i, 3) & y(i, 4)) Then
            If delRng2 Is Nothing Then
                Set delRng2 = ws2.Range(dict2.Item(y(i, 1) & y(i, 2) & y(i, 3) & y(i, 4)))
            Else
                Set delRng2 = Union(delRng2, ws2.Range(dict2.Item(y(i, 1) & y(i, 2) & y(i, 3) & y(i, 4))))
            End If
        End If
    Next i
    
    
    If Not delRng1 Is Nothing Then delRng1.EntireRow.Delete
    If Not delRng2 Is Nothing Then delRng2.EntireRow.Delete
    
    lr2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row
    
    y = ws2.Range("A1:D" & lr2).Value
    
    For i = 1 To UBound(y, 1)
        If dict1.exists(y(i, 1) & y(i, 2) & y(i, 3) & y(i, 4)) Then
            ws2.Range("E" & i).Value = "Duplicate"
        End If
    Next i
    
    Application.ScreenUpdating = True
    End Sub
    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. [SOLVED] Record macro on filtering date, wrong result after running the macro
    By karmapala in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-16-2019, 05:32 AM
  2. Replies: 4
    Last Post: 03-13-2017, 07:49 AM
  3. [SOLVED] Macro for user defined report, filtering issue within Macro
    By Mlanger575 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-15-2016, 06:24 AM
  4. [SOLVED] Filtering via macro?
    By OpOrange in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-05-2015, 04:11 AM
  5. [SOLVED] Filtering Macro
    By sandiptemkar in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-01-2012, 06:38 AM
  6. Macro for Filtering
    By gloring in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-28-2008, 10:18 AM
  7. Filtering in a macro
    By sungen99 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-09-2005, 02:44 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